Power BI · Week 2 of 4 · 5–6 hours
Power Query and Data Transformation
Clean and reshape data in Power Query so it is ready to model.
Learning objectives
By the end of this week you will be able to:
- Open the Power Query Editor and read the Applied Steps list.
- Set data types, remove columns and rows, and rename fields.
- Split, replace, trim, fill and filter data.
- Combine queries with Append and Merge.
Lessons
Power Query
Power Query is the data preparation tool built into Power BI (and Excel). Open it with Home > Transform Data.
The editor has the queries list on the left, a data preview in the middle, and the Applied Steps list on the right. Every change you make is recorded as a step. When the data is refreshed next month, Power BI replays every step automatically. You clean the data once and it stays clean.
You can click any step to see the data at that moment, delete a step to undo it, or drag steps to reorder them. Power Query writes these steps in a language called M, but you do not need to write M yourself; the buttons write it for you.
Tip: turn on View > Column quality and Column distribution. They show the percentage of valid, error and empty values in each column, which makes problems obvious.
Data Transformation
The transformations you will use most (right-click a column header or use the ribbon):
| Task | How |
|---|---|
| Set the data type | Click the icon left of the column name (ABC, 123, calendar). Do this first. |
| Remove columns you do not need | Select them > Remove Columns (or Choose Columns) |
| Use the first row as headers | Home > Use First Row as Headers |
| Remove blank rows or duplicates | Home > Remove Rows |
| Clean text | Transform > Format > Trim, Clean, Capitalize Each Word |
| Fix values | Replace Values (for example "N. America" → "North America") |
| Split a column | Split Column by delimiter (for example "City, State") |
| Fill gaps from the row above | Fill > Down |
| Create a calculated column | Add Column > Custom Column, or Column From Examples |
| Turn wide month columns into rows | Select the month columns > Unpivot Columns |
Combining queries:
- Append stacks tables with the same columns on top of each other (January sales + February sales).
- Merge joins two tables side by side on a matching column, like XLOOKUP or a SQL JOIN.
When finished, click Close & Apply to load the clean data into the report.
Watch
- VideoPower BI Tutorial for Beginners 20 - Transforming Data with Power Query
Walks through the Power Query Editor and common steps.
- VideoHow to Transform Data in Power BI - Use Power Query to Prepare Data for Analysis
More transformations including split, replace and unpivot.
Practice
Your instructor will provide (or you can create) a messy file with: a title row above the headers, extra spaces, inconsistent region names, a combined "City, State" column, blank rows and numbers stored as text. In Power Query, fix every problem, and check that each fix appears in Applied Steps. Then append a second month's file with the same layout. Click Close & Apply.
Check your understanding
Try each question first, then open it to see the answer.
Why does Power Query record Applied Steps?
So the same cleaning is repeated automatically every time the data refreshes.
What is the difference between Append and Merge?
Append stacks rows from tables with the same columns; Merge joins tables side by side on a key.
Which transformation turns 12 month columns into a single Month column?
Unpivot Columns.
Assignment
Submit your .pbix file and a screenshot of the Applied Steps list with one sentence explaining each step.