Power Query is the data-preparation tool built into both Power BI and Excel. Its superpower: every cleaning step you take is recorded, so next month's file is cleaned with one click on Refresh.
Opening Power Query
- Power BI: Home โ Transform data.
- Excel: Data โ Get Data โ choose your source, then Transform Data.
The window shows your data, the list of Applied Steps on the right and a formula bar showing each step in the M language. You rarely need to write M yourself.
Essential steps
Set the right data types
Click the type icon in each column header. Dates must be Date, amounts Decimal Number, IDs usually Text. Wrong types cause wrong sorting, failed sums and broken relationships later.
Promote headers and remove junk rows
Excel exports often have title rows above the real header. Use Remove Top Rows, then Use First Row as Headers. Remove Blank Rows clears empty lines.
Trim and clean text
Transform โ Format โ Trim, Clean and Capitalize Each Word. Then Replace Values to merge spelling variants.
Split columns
Split Column by Delimiter (for "Pune - West") or by Number of Characters (for codes).
Add custom and conditional columns
Add Column โ Conditional Column creates if-then labels without code. Custom Column allows formulas such as [Revenue] / [Units].
Unpivot
Month columns (Jan, Feb, Mar) should become rows. Select the columns that identify each row, then Transform โ Unpivot Other Columns. The result has Attribute (month) and Value columns, which is the shape Power BI models want.
Group By
Summarise within Power Query when you do not need detail rows: Transform โ Group By.
Combining data
Append: stack files
Same columns, more rows: January, February and March files. Home โ Append Queries. Even better, use Get Data โ Folder and Combine Files to append every file in a folder automatically; next month, drop the new file in the folder and Refresh.
Merge: join tables
Home โ Merge Queries joins on matching columns, like a SQL join. Choose the join kind (Left Outer is the usual choice), then expand only the columns you need.
Good habits
- Rename steps to describe what they do ("Removed cancelled orders").
- Remove columns early that you do not need; the model becomes smaller and faster.
- Keep raw queries separate: reference a raw query, then clean in a new one.
- Check row counts after merges and filters (the status bar shows them).
- Watch for errors: column quality and column profile (View tab) show errors and empty values per column.
Power Query or DAX?
Shape and clean data in Power Query; calculate measures in DAX. If something is about the data itself (types, text fixes, unpivoting), it belongs in Power Query.
Practise
The motor claims Power BI project starts with raw claim extracts that need exactly these steps before any dashboard work. Read Power BI for beginners for how Power Query fits with the model and DAX.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: