Power Query and Power Pivot in Excel: From Raw Data to a Data Model
Power Query and Power Pivot in Excel is an advanced-level course of 5 lessons (about 1 h 35 min), with 15 exercises and 15 quiz questions. Prerequisites: Excel: PivotTables. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Excel
- Advanced
- 5 lessons
- 1 h 35 min
- 15 exercises
- Windows virtual machine (phase 2)
The course in brief
This course brings Excel into the BI era. You prepare data with Power Query, combine several files, build a related data model with Power Pivot and write your first DAX measures. Finally, you learn to refresh and parameterize your workbooks, then carry them over to Power BI: the same data and the same queries as the BI1 and BI2 courses.
What you will be able to do
- Import and clean files in Excel with Power Query
- Combine several sources with append, merge and the Folder connector
- Build a star-schema data model with relationships and a date table
- Write DAX measures with SUMX, DIVIDE, CALCULATE and ALL
- Refresh, parameterize and troubleshoot queries, then migrate the workbook to Power BI
Syllabus: 5 lessons
- Import a CSV file into Excel with Power Query
- Apply common cleanups and read the applied steps
- Choose the load destination: table, connection only or data model
- 02
Combining Several Sources: Append and Merge
- Stack files with the same structure using Append Queries
- Enrich one table with another using Merge Queries
- Choose the join type and check the result
- 03
The Data Model and Power Pivot
- Load several tables into the data model
- Create relationships between tables in Diagram View
- Build a PivotTable from the model
- 04
First DAX Measures
- Distinguish a calculated column from a measure
- Write simple measures with SUM, SUMX, COUNTROWS and DIVIDE
- Change the context of a calculation with CALCULATE and ALL
- 05
Refreshing, Parameterizing and Moving to Power BI
- Refresh queries and the model, and set refresh options
- Parameterize a query from a workbook cell
- Know when and how to move from Excel to Power BI
Final project
The Steering Workbook of a Multi-Store Retailer
This course's fictional retailer sends you its monthly sales files, its store reference table and a customer export to clean. Build an Excel workbook that consolidates the sales from a folder, cleans the customers, organizes a star-schema data model with a date table, calculates DAX measures and presents a dashboard of PivotTables and slicers. The source path must be parameterizable, and the workbook must be importable into Power BI Desktop without reworking the measures.
Deliverables
- An .xlsx file with the queries Ventes (15 rows, CA 505), Magasins, Clients (5 rows, CA 4,580) and DossierSource
- A data model with the Magasins-Ventes and Calendrier-Ventes relationships
- The measures CA, Nb ventes, Panier moyen, CA Boissons and Part du CA, formatted
- A dashboard sheet with at least two PivotTables and a slicer by region
- An anti-join check query and a note describing the refresh procedure
Assessment criteria
- Named queries, intermediate ones as connection only, no hard-coded path
- Correct star model, valid relationships, months sorted chronologically
- Correct measures (505, 33.67, 186), reused within one another
- Successful refresh after adding a file to the folder
- Successful import into Power BI Desktop
Frequently asked questions
Is Power Pivot available in every version of Excel?
Power Pivot is an Excel add-in for Windows; depending on the edition, some features may be missing, and Excel for Mac does not offer it. Power Query is more widely available.
Do I need to have taken the Power BI course?
No. The course assumes you are comfortable with PivotTables. On the contrary, it prepares you for the Power BI courses, which reuse the same data and the same M language.
What is the difference between a Power Query merge and a Power Pivot relationship?
A merge copies columns from one table into another during preparation. A relationship keeps the tables separate and links their keys in the model, which is lighter and more flexible for analysis.
Are DAX formulas in French in Excel?
No, DAX function names stay in English. Only the argument separator generally follows the regional settings: the semicolon in a French-language Excel.
This course, told for your job
Say what you want to be able to do: “Power Query and Power Pivot in Excel” takes its place in your path, with examples from your field. The concepts stay those of the course.