Excel Expert Data Management: Power Query and Power Pivot

Learn Power Query and Power Pivot in Excel to import, clean and connect data for repeatable reporting. Build data models, manage relationships, create measures and define KPIs after establishing a solid PivotTable foundation.

View dates and fees

Level: Expert
Length: 4 hours
Public class size: Up to 10

Who this course is for

Excel users with a PivotTable foundation who need repeatable data preparation and data models

What you will work on

Import data with Power Query
Clean irregularly formatted data
Connect to data sources
Automate data updates
Refresh PivotTable and PivotChart summaries
Build Power Pivot data models
Manage relationships
Create measures and KPIs

Outline checked against the Excel Expert Data Management booking page on 2026-09-11. Confirm any must-have feature or software version with us before registering.

Choose your starting point

The published prerequisite is Excel Advanced Data Management or equivalent experience.

Review the topics in Excel Advanced Data Management to compare your current skills.

Browse the Excel course comparison or ask for help choosing a course.

What comes with the public course

90 days of course video access with class exercise files
A free retake of the same course for one year
A Power Concepts certificate of completion

Already registered? Use student sign-in for your account, or find course manuals and exercise files.

Excel course questions

What should I know before Excel Expert Data Management?

The published prerequisite is Excel Advanced Data Management or equivalent experience.

Where can I check dates and fees?

Use the course booking links on this page to see the available sessions and their current prices. Check the session time zone, delivery format, currency and tax total before completing registration.

Can my team take this training together?

Contact Power Concepts to discuss a private session. Tell us the number of learners, their current experience, the work they need to do and the software versions they use so we can recommend a suitable scope.

What certificate does the course include?

The course includes a Power Concepts certificate of completion. It is not a vendor certification exam or a professional accounting qualification.

Dates and registration

Dates and fees: Excel Expert Data Management

Choose a session on the booking site, then review the time zone, delivery details and final fee before registering.

Compare courses and training options

Course
Excel Pivot Tables and Data Validation Course
Prepare reliable Excel data and summarise it with PivotTables and PivotCharts. This course covers data validation, duplicates, advanced filters and slicers; the separate Expert Data Management course covers Power Query and Power Pivot.
Guide cluster
Excel Data & Reporting
Tables, filters, pivot tables, slicers, and Power Query — how to turn a raw export into a report you can refresh instead of rebuild.
For teams
Excel Training for Teams and Businesses
Plan Excel training around your team’s starting skills, recurring spreadsheet tasks and the results learners need to check.
On this site
Excel Skills Assessment: Choose Your Course
Eight questions, no signup, and a recommended course at the end.

Move from repeated cleanup to a repeatable process

Excel Expert Data Management covers Power Query imports and cleanup, refreshed summaries, Power Pivot models, relationships, measures and KPIs. The published prerequisite is Advanced Data Management.

For readiness, explain how you currently receive data, what you change and how you verify the final total. A repeatable process needs both a transformation method and a check for changed source files. Confirm your Excel edition and source requirements before booking; access to every possible connector is not assumed.

Choose a model when one table is no longer enough

Power Pivot introduces a model with related tables and measures. In a sample sales report, keep transactions separate from product attributes instead of copying the product description into every manually maintained report. Agree which identifier connects the tables and verify that it is unique where required.

Expert Data Management explicitly covers Power Pivot models, relationships, measures and KPIs. Ask about the model you need and the Excel environment available to you; the software version and data structure affect which exercises can be used.

Prepare your data tools for the online session

Open Excel before class and check that you can access the data-import tools required by the course. Use the supplied exercise files in a folder you can find, and ask your IT team about any restrictions on connectors or downloads. A blocked data source is a setup issue, not a learning failure.

Expert Data Management covers importing, cleaning and refreshing data with Power Query. Confirm your operating system and Excel build before booking so the class can address the correct tools rather than assuming all installations are identical.

Plan a Canadian team’s Power Query attendance

For a group attending from different offices, collect each participant’s local working hours, Excel version and access restrictions. Select a session whose stated time zone fits those windows. Canadian delivery does not imply that a published session automatically starts at the same local time everywhere.

Use a sanitised export that everyone can open for the first practice task. Corporate enquiries can then describe the recurring source files, refresh deadline and reporting owner, which makes the requested outcome more concrete than a request for general data training.

Estimate the full cost of repeatable reporting training

Check the current fee for Expert Data Management on its booking page. Also allow time to prepare safe source files, attend and rebuild one import without assistance. If several staff maintain the same report, compare individual registrations with a team enquiry that includes handover requirements.

A useful budget distinguishes learning from implementing a production solution. Training can develop the staff’s skills; rebuilding a complex reporting system, maintaining credentials or supporting a connector may require a separately scoped project.

Merge tables with a tested matching key

A merge combines information by matching fields. For a practice task, match an order’s product code to a product list, then deliberately include one missing code and one duplicated product record. Inspect the result before accepting its totals: a duplicate match can multiply rows.

Expert Data Management covers Power Query imports and cleaning. Ask to include a merge example if that is central to your work, and bring the proposed keys and expected row count so the exercise tests the relationship as well as the interface.

Append rows that describe the same kind of record

Appending monthly files makes sense when they use compatible columns and represent the same unit of activity. Keep a source-month or source-file field so an unexpected total can be traced back. Before combining, compare column names, date types and the meaning of blank values.

A useful practice task includes one file with an extra heading and one with a missing column. Discuss how the workflow should identify those exceptions rather than treating every file dropped into a folder as automatically trustworthy.

Control what enters a folder import

Use a dedicated input folder and separate it from output reports, archived files and temporary downloads. Establish the file pattern and required headings before combining files. Otherwise, a copy of last month’s result can be imported as though it were new source data.

For the exercise, add an approved new file and one intentionally incompatible file, then review the refresh outcome. Describe your folder conventions and access permissions when asking for a Power Query workshop built around this recurring task.

Set types deliberately during preparation

A product code that begins with zero should not be treated like a quantity, and a date should not depend on a guess about regional formatting. Write a simple data dictionary with the required type for each column and a valid example. Check errors after the conversion step.

Use a practice file containing an ambiguous date, a text amount and a leading-zero identifier. The goal is a repeatable rule for interpreting the source, plus a visible exception process for values that cannot be converted correctly.

Unpivot a report into useful records

A worksheet with one column for each month can be convenient to read but awkward to analyse as a growing dataset. An unpivot exercise turns those month headings into a period field and their values into an amount field, while preserving the fields that identify the original record.

Test the process when another month is added. Discuss which columns identify the record and which should become rows; this distinction helps avoid moving descriptive fields into the values column by mistake.

Make refresh a controlled routine

A successful refresh should have a named owner, a known source location and a way to check that new data arrived. Compare the latest source period, row count and a control total before distributing the result. A refresh finishing without an error does not establish that the source is current.

Expert Data Management includes automated updates and refreshed summaries. Bring the refresh frequency and handover requirements to the course discussion so the workflow can be practised as a repeatable operating task.

Keep an exception path for failed data

Create a sample with one invalid date or amount, then find the step where it becomes an error. Decide whether the record should be corrected, excluded with a reason or routed to an exception report. Removing errors without tracking their effect can change the report’s scope.

A useful Power Query exercise compares the accepted and rejected row counts with the input. Ask for an error-handling scenario if maintaining imports is your main responsibility, especially where sources change independently of the reporting team.

Build relationships around a clear record definition

Start with two small tables: one row per product in a product list, and one row per sale in a transaction list. Identify the shared key and test that every sale finds the intended product. Repeated identifiers in the reference table deserve investigation before a relationship is trusted.

Power Pivot models and relationships are explicit topics in Expert Data Management. A model-focused enquiry should describe each table’s grain, keys and required measures rather than only sending a screenshot of the desired chart.

Check readiness for the Power Query pathway

Expert Data Management explicitly requires Advanced Data Management or equivalent experience. A practical equivalent includes understanding a structured source table and being able to build and check a PivotTable summary. You do not need to guess your level from the word Expert alone.

If you already maintain reports, describe your current process, the data sources and the step that takes the most manual work. Power Concepts can then help you compare the published advanced and expert outlines against your actual starting point.