Power Query for beginners: clean messy data without formulas

Power Query is the part of Power BI and Excel that cleans data before anyone reports on it — removing columns, fixing dates, splitting names, stacking twelve monthly files into one table. You do it by clicking, not writing formulas, and every step is recorded, so next month's file cleans itself.

What is Power Query, exactly?

Microsoft's own definition is refreshingly plain: Power Query is "a data transformation and data preparation engine" with "a graphical interface for getting data from sources and a Power Query editor for applying transformations" (Microsoft Learn).

Three properties make it worth your afternoon:

It is repeatable. Cleaning a spreadsheet by hand solves today's problem. Power Query records what you did as a query you can refresh, so it solves every future month's problem too.

It is non-destructive. Power Query records transformations as query steps and applies them when the query runs — it does not modify the source data. Your original file is untouched, so there is no way to break anything by experimenting.

It is everywhere. The same engine runs in Power BI, Excel for Windows and Mac, Power Apps, Power Automate, Azure Data Factory, Data Factory in Fabric and SQL Server Analysis Services. Learn it once, use it in all of them.

If you are new to the wider tool, our beginners' guide to Power BI part one covers installing Desktop, and part two covers connecting to sources — this post picks up at the moment the data arrives and looks wrong.

Where do I find it?

In Power BI Desktop, click Transform data on the Home ribbon. In Excel, it is the Get & Transform Data group on the Data tab.

Either way you land in the Power Query editor, which has three parts worth knowing:

• The data preview in the middle — a sample of your table, not the whole thing.
• The Applied Steps pane on the right — the recorded list of everything you have done.
• The ribbon at the top — several hundred transformations, grouped under Home, Transform and Add Column.

Applied Steps is the important one. It is an ordered, editable, deletable history. Click any step to see the table as it was at that moment. Made a mistake six steps ago? Delete that step; the rest re-run. This is why Power Query is safe for beginners in a way that a wall of nested Excel formulas is not.

The eight transformations that do most of the work

You do not need the several hundred. You need these.

Remove columns. Most source extracts carry columns nobody will ever use. Right-click, Remove. Fewer columns means a smaller, faster model.

Use first row as headers. Exports frequently arrive with the real headings sitting in row one and useless Column1, Column2 names above them. One button fixes it.

Change type. The single most common cause of a broken report is a date column that Power Query has decided is text. Set types explicitly, early, and set them once.

Filter rows. Drop the blank rows, the subtotal rows and the "TOTAL" line your finance system helpfully appends. Subtotal rows inside a data table cause double-counting later, and it is a nasty bug to find.

Split column. "Smith, John" becomes two columns by delimiter. So does a postcode, a product code, or a date stuck to a time.

Unpivot. The one that feels like magic. Spreadsheets are usually laid out with months across the top — Jan, Feb, Mar as separate columns. Reporting tools want one row per month. Select the month columns, choose Unpivot Columns, and a wide human-readable sheet becomes a tall machine-readable table. If you learn one thing from this post, learn this one.

Merge queries. A lookup: bring the customer's region in from a second table by matching on customer ID. This is Excel's VLOOKUP, done properly and repeatably.

Append queries. Stack tables with the same shape on top of each other — twelve monthly files into one year. Point it at a folder and every new file dropped in gets picked up on the next refresh.

A worked clean-up

A sales export arrives monthly. It has a title row above the headings, months spread across columns, a "TOTAL" row at the bottom, and customer IDs but no customer names.

1. Connect to the file (Get data, then Transform data rather than Load — always go to the editor first).
2. Remove top rows: delete the title row.
3. Use first row as headers: promote the real headings.
4. Filter rows: remove the row where Customer ID is "TOTAL".
5. Unpivot the twelve month columns into Month and Value.
6. Change type: Month to date, Value to decimal number.
7. Merge queries with the customer table on Customer ID; expand to bring in the name and region.
8. Close & Apply.

That is eight clicks-worth of work. Next month, drop in the new file and hit Refresh — all eight steps re-run in order. The month after that, the same. This is the whole argument for Power Query in one example: you did the thinking once.

Do I need to learn M?

Not to do the above. Power Query writes the underlying code — the M formula language — for you as you click, and the Advanced Editor lets you read or edit it when you want to.

You will eventually meet M in three situations: when you want a transformation the ribbon does not offer, when you want to parameterise a query, and when you are reading someone else's work. When that day comes, Microsoft's M formula language reference is the specification. That is a later problem. Start by clicking.

Three habits that save pain later

Rename your steps. Applied Steps default to names like Changed Type1. Right-click, Rename, describe what it does. Six months on, you will be grateful.

Do the filtering early. Remove rows and columns near the top of the step list, so every later step works on less data.

Do shape work here, not in DAX. If a change alters the shape of the table — columns, rows, types — it belongs in Power Query. If it produces a number on a report, it belongs in DAX. Getting this boundary right is most of what separates a fast model from a slow one, and it pairs directly with modelling your tables as a star schema.

Cleaning data properly is unglamorous and it is where reporting projects are actually won. Our data practice and Power BI consultancy pages set out how we do this on client work.

Frequently asked questions

What is Power Query?

Power Query is Microsoft's data transformation and preparation engine. It gives you a graphical editor for connecting to a data source and applying transformations, and it records every step you take so the whole clean-up can be re-run against new data with one refresh. It transforms data but does not store it — the host product, such as Power BI or Excel, decides where the results land.

Do I need to learn code to use Power Query?

No. Every transformation is available from the ribbon, and Power Query writes the underlying M code for you as you click. You only need the Advanced Editor and the M language when you want something the interface does not offer, which for most business reporting is rare.

Where do I find Power Query?

In Power BI Desktop it is the "Transform data" button on the Home ribbon. In Excel for Windows and Mac it is the Get & Transform Data group on the Data tab. The same engine also runs in Power Apps, Power Automate, Azure Data Factory, Data Factory in Microsoft Fabric and SQL Server Analysis Services.

What is the difference between Power Query and DAX?

Power Query shapes the data before it reaches your model — removing columns, fixing types, unpivoting, merging tables. DAX calculates over the data once it is in the model — measures, totals, year-on-year comparisons. A useful rule of thumb: if it changes the shape of the table, it belongs in Power Query; if it produces a number on a report, it belongs in DAX.

Will Power Query change my original spreadsheet?

No. Power Query records transformations as query steps and applies them when the query runs; it does not modify the source data. Your original file stays exactly as it was, which is what makes the whole approach safe to experiment with.

---

Easy Insight is a UK consultancy for AI, web, apps and data — senior specialists only, no juniors.