Power Query: Repeatable Data Preparation
Cleaning by hand works once. When a new file arrives next month, you have to do it all again. Power Query records each cleaning step and replays them when you refresh. The same tool is built into Power BI, so this lesson carries straight into the next course.
Get data
On the Data tab, choose Get Data and pick a source: an Excel workbook, a CSV file, a folder of files, a database or a web page. To use a table already in the workbook, click in it and choose Data > From Table/Range.
The Power Query Editor opens with a preview of the data.
Applied Steps
Every action you take is added to the Applied Steps list on the right. Click a step to see the data as it was at that point. Delete a step to undo it. The original file is never changed.
Each step is written in a formula language called M, which you can view in the formula bar. You do not need to write M to use Power Query.
Common transformations
| Task | Where |
|---|---|
| Remove or reorder columns | Right-click the column header |
| Change a data type | Click the type icon in the header |
| Remove extra spaces | Transform > Format > Trim |
| Split a column | Transform > Split Column |
| Filter rows | Drop-down in the column header |
| Remove duplicates | Home > Remove Rows > Remove Duplicates |
| Replace values | Right-click > Replace Values |
| Add a calculated column | Add Column > Custom Column |
| Fill blanks from the row above | Transform > Fill > Down |
Set the data type of every column. Wrong types are the most common cause of problems later.
Unpivot
Data often arrives with one column per month:
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Laptop | 2 | 1 | 0 |
Analysis needs one row per product and month. Select the Product column, then choose Transform > Unpivot Columns > Unpivot Other Columns. The result has three columns: Product, Attribute (the month) and Value.
Combining tables
- Append Queries stacks tables with the same columns, for example January and February files.
- Merge Queries joins two tables on a matching column, like a lookup. You choose the join kind, such as Left Outer to keep every row of the first table.
To combine every file in a folder, use Get Data > From File > From Folder. New files dropped into the folder are included on the next refresh.
Load and refresh
Choose Home > Close & Load to send the result to a worksheet table, or Close & Load To for a PivotTable or a connection only.
When the source changes, choose Data > Refresh All. Every step runs again on the new data.
Practice
Load the Sales table into Power Query, add a Revenue column there, filter out rows with fewer than 2 units, and load the result to a new sheet.