Power Query: Import, Clean and Transform Your Data in Power BI
Power Query: Import and Transform is a beginner-level course of 5 lessons (about 1 h 30 min), with 17 exercises and 15 quiz questions. Prerequisites: Getting Started with Power BI Desktop. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Power BI
- Beginner
- 5 lessons
- 1 h 30 min
- 17 exercises
- Windows virtual machine (phase 2)
The course in brief
Power Query is Power BI's data preparation workshop. In this course, you learn to read and edit the steps of a query, clean a real file, create columns, combine several sources and reshape tables. You also discover the M language hiding behind every click, and the best practices that make your refreshes reliable and fast.
What you will be able to do
- Navigate the Power Query editor and read an M query
- Clean a file: types, locale, blanks, duplicates, text
- Create custom, conditional and split columns
- Combine tables by appending and merging, choosing the right join
- Unpivot and group data
- Organize queries with references, parameters and controlled loading
Syllabus: 5 lessons
- Open the Power Query editor and identify its areas
- Read the list of applied steps and the formula bar
- Understand the let ... in structure of an M query
- 02
Cleaning Data: Types, Blanks, Duplicates, Text
- Apply common cleaning transformations
- Handle blank values, duplicates and errors
- Use locale settings to type dates and numbers
- 03
Adding Columns: Custom, Conditional, Split
- Create a custom column with an M formula
- Create a conditional column without code
- Split and extract information from a column
- 04
Combining Queries: Append and Merge
- Stack tables with the same structure using Append Queries
- Join two tables with Merge Queries and choose the join type
- Automatically combine the files in a folder
- 05
Reshaping (Unpivot, Group) and Best Practices
- Unpivot a cross-tab into a usable table
- Group rows with aggregations
- Apply best practices: parameters, references, loading, query folding
Final project
Preparing Data for a Multi-Store Retailer
The retailer sends you four files: ventes.csv (first quarter), ventes_avril.csv, magasins.csv and objectifs.csv (wide format), as well as clients_brut.csv to clean. In Power Query, build a set of clean, documented queries ready for modeling: consolidated sales with amount and region, targets in long format, deduplicated customers, and a quality-control query that lists anomalies.
Deliverables
- A .pbix file containing the queries Ventes (15 rows, total Montant 505), Magasins, Objectifs (9 rows) and Clients (5 rows)
- A control query using an anti join
- A DossierSource parameter used by all the sources
- A screenshot or an export of the advanced editor of the Ventes query
Assessment criteria
- Correct types on all columns, locale handled properly
- Exact numerical results (505, 495, 4,580)
- Queries and steps explicitly named, organized in groups
- Intermediate queries not loaded
- Relevant use of joins and unpivoting
Frequently asked questions
Do you need to learn the M language to use Power Query?
No, most of the work is done with the mouse. Being able to read M does help you understand, fix and reuse your queries, though; this course introduces you to it gradually.
Is Power Query the same in Excel and in Power BI?
The engine and the M language are the same. The interface is very similar; only a few connectors and options differ.
What is the difference between merging and appending queries?
Append stacks the rows of tables with the same structure. Merge adds columns from another table by matching a key, like an SQL join.
Does Power Query modify my source files?
No. Power Query reads the sources and applies its steps to the result. The original files remain untouched.
This course, told for your job
Say what you want to be able to do: “Power Query: Import and Transform” takes its place in your path, with examples from your field. The concepts stay those of the course.