Clean messy data once, then refresh it with one click every month. Power Query is the cleaning tool built into Excel and Power BI.
What is Power Query?
Power Query imports data from files, folders, databases and web pages, and cleans it with a list of recorded steps. Each step — remove a column, fix a date, trim spaces — is saved in the Applied Steps list. When new data arrives, you click Refresh and every step runs again on the new data.
Analysts often say that most of their time goes on cleaning data. Power Query is how you stop doing that cleaning by hand.
An everyday example
Every month, three branches — Kochi, Thrissur and Kozhikode — send a CSV of their sales. The files have a title row at the top, extra spaces in product names, dates stored as text and a blank row at the end. With Power Query, you clean them once and combine all three into a single table. Next month, you drop the new files in the folder and press Refresh.
Step 1: Get the data
- One file: Data › Get Data › From File › From Text/CSV
- All files in a folder: Data › Get Data › From File › From Folder, then Combine › Combine & Transform Data
- A table in the same workbook: select it and use Data › From Table/Range
In the preview, click Transform Data to open the Power Query Editor.
Step 2: Clean it
| Problem | Fix in Power Query |
|---|---|
| Title rows above the headers | Home › Remove Rows › Remove Top Rows, then Use First Row as Headers |
| Columns you don’t need | Select the columns you want, then Home › Remove Columns › Remove Other Columns |
| Extra spaces | Transform › Format › Trim |
| Dates or numbers stored as text | Click the type icon in the column header and choose Date or Decimal Number |
| Blank rows | Home › Remove Rows › Remove Blank Rows |
| “Kochi – Ernakulam” in one column | Home › Split Column › By Delimiter |
| Category only on the first row of each group | Transform › Fill › Down |
| Months spread across columns (Apr, May, Jun…) | Select the ID columns, then Transform › Unpivot Other Columns |
| Duplicate rows | Home › Remove Rows › Remove Duplicates |
Step 3: Combine tables
- Append stacks tables with the same columns, one below the other: Home › Append Queries. Use it for monthly or branch files.
- Merge brings columns from another table by matching a key, like XLOOKUP: Home › Merge Queries. Use it to add a product category to sales rows.
Step 4: Load it
Click Home › Close & Load To… and choose:
- Table to load the result to a worksheet, for smaller data.
- Only Create Connection and tick Add this data to the Data Model for large data. The data goes straight into the Data Model used by Power Pivot, with no worksheet row limit.
Step 5: Refresh
When the source data changes, go to Data › Refresh All or press Ctrl + Alt + F5. Every step runs again on the new data.
FAQ
Do I need to learn the M language?
No. Every button you click writes M code for you. You can view it in Home › Advanced Editor later, when you are curious.
Is Power Query available on Mac?
Excel for Mac has Power Query with fewer data sources than Windows. Excel for Windows and Power BI Desktop have the full version.
Should I clean data in Power Query or with formulas?
Use Power Query for cleaning that repeats every time new data arrives. Use formulas for one-off fixes and for calculations on the cleaned data.