Most of a Power BI developer’s time isn’t spent on charts. It’s spent getting data into shape. Power Query is the tool for that, built into Power BI Desktop (and Excel). This tutorial covers the steps you’ll use on almost every file.
How Power Query works
Open it with Home → Transform data. You’ll see three things:
- Queries (left): one per table.
- Data preview (middle): the first rows, after every step so far.
- Applied steps (right): the recipe. Every click you make becomes a step.
The key idea: you never edit the source data. You record steps, and Power Query replays them on every refresh. Next month’s file gets cleaned the same way automatically.
Step 1: Check the data types
The icon at the left of each column header shows its type: ABC for text, 123 for whole numbers, 1.2 for decimals, a calendar for dates.
Wrong types cause wrong results. A Sales column stored as text can’t be summed. Click the icon and pick the right type. Power BI adds a Changed Type step on its own when you load a file; check it, because it guesses.
Step 2: Remove what you don’t need
Select the columns you need, then Home → Remove Columns → Remove Other Columns. Fewer columns mean a smaller, faster model.
Choose Remove Other Columns rather than removing the unwanted ones: if the source later gets a new column, it won’t sneak into your model.
Step 3: Clean text
- Transform → Format → Trim removes spaces at the start and end.
"Canada "and"Canada"look the same but don’t match in a relationship. - Clean removes invisible characters.
- Replace Values fixes spellings:
USA→United States of America.
Step 4: Split and combine columns
- Split Column → By Delimiter turns
Smith, Annainto two columns. - Merge Columns joins columns with a separator.
- Add Column → Column From Examples: type what you want for a couple of rows and Power Query writes the formula.
Step 5: Unpivot
Spreadsheets often have one column per month: Jan, Feb, Mar… Power BI wants one row per month instead. Select the columns that should stay (like Product), then Transform → Unpivot Other Columns. You get Attribute (the month) and Value columns. Rename them and fix the types.
Step 6: Combine tables
- Append stacks tables with the same columns, like January’s file on top of February’s.
- Merge joins tables side by side on a matching column, like SQL’s JOIN. Pick the join kind carefully: Left Outer keeps every row of the first table.
Step 7: Close & Apply
Home → Close & Apply loads the result into Power BI. Every refresh from now on repeats all your steps.
Power Query or DAX?
A simple rule: shape data in Power Query, calculate on the model with DAX. Cleaning, typing, splitting and unpivoting belong in Power Query. Ratios, totals and time comparisons belong in DAX measures. The full rule, with the grey areas, is in Power Query or DAX.
Practise it
The Power Query lessons open a practice Power Query Editor with messy data: you fix types, trim text, unpivot and merge, and each step is checked.
Sources
- Power Query documentation, Microsoft Learn
- Unpivot columns, Microsoft Learn
- Merge queries overview, Microsoft Learn