Power Query: 10 Data Cleaning Steps Every Power BI Analyst Needs

Power Query is the data preparation tool inside Power BI and Excel. It records each cleaning action as a step, so the same cleaning runs again automatically on every refresh. The ten steps that fix most real-world data are: profile the columns, set data types, remove junk rows, promote headers, trim and clean text, split or extract columns, unpivot cross-tab data, fill down, merge and append queries, and handle errors and nulls deliberately. Get these right and your data model and DAX become much simpler.

I'm Gulab Chand Tejwani, a Microsoft Certified Trainer and PL-300 certified Power BI analyst. In our Power BI batches in Anand, I spend more time on Power Query than most students expect, because almost every "my DAX is wrong" problem I'm asked about turns out to be a data problem that should have been fixed upstream. These are the ten steps I teach first, roughly in the order I apply them.

Why clean data in Power Query instead of Excel?

You can clean a file in Excel by hand. The trouble is you have to do it again next month. Power Query records each action in the Applied Steps pane as M code, and on refresh it replays every step on the new data. Clean once, refresh forever, as long as the source keeps roughly the same shape.

It's also the first section of the PL-300 exam. "Prepare the data" carries 25 to 30% of the marks, and it lists profiling, cleaning, data types, pivot and unpivot, merge and append, and creating fact and dimension tables. Microsoft's Power Query documentation covers every transformation below in detail.

1. Profile the data before you touch it

Turn on Column quality, Column distribution and Column profile from the View tab. Then change "Column profiling based on top 1,000 rows" (bottom left of the editor) to "entire data set". The default only looks at the first thousand rows, which is how errors on row 50,000 slip through.

Look for three things: the percentage of errors and empty values, columns with suspiciously few distinct values, and text that should be numbers. Microsoft's guide to the data profiling tools explains each view.

2. Set data types early and on purpose

Power Query guesses types with an automatic "Changed Type" step. Sometimes it guesses wrong, especially with dates in Indian format (DD/MM/YYYY) when your system locale is US. Delete the automatic step if it's wrong and set types yourself. For dates in a different format, right-click the column and choose Change Type, then Using Locale, and pick English (India).

Wrong types cause quiet problems later: numbers stored as text won't sum, and dates stored as text won't work with time intelligence.

3. Remove junk rows: top, bottom, blanks and totals

Exported reports often come with a title, a few blank rows, and a total row at the bottom. Use Remove Rows, then Remove Top Rows, Remove Bottom Rows or Remove Blank Rows. Then filter out "Total" or "Grand Total" lines with a text filter.

Leaving a total row in the data is the classic way to double every number in your report. I've watched it happen in a board meeting. Not mine, luckily.

4. Promote headers (and fix their names)

Once the junk is gone, use Use First Row as Headers. Then rename columns to something a business user would understand. "Cust_Nm_1" becomes "Customer Name". Column names show up in every visual, tooltip and Q&A question, so this small step pays off across the whole report.

5. Trim, clean and fix text case

Text from forms and ERP exports carries leading spaces, trailing spaces and invisible characters. "Anand " and "Anand" look identical but group separately. Select the text columns and use Format, then Trim and Clean. Use Capitalize Each Word or UPPERCASE where it helps consistency.

In M, the steps look like this:

= Table.TransformColumns(
    Source,
    {{"City", each Text.Proper(Text.Trim(Text.Clean(_))), type text}}
)

6. Split or extract columns

One column often holds two facts: "INV-2026-0042", "Patel, Ramesh" or "Anand - Gujarat". Split Column by Delimiter separates them. Extract (Text Before Delimiter, Text After Delimiter, First Characters) pulls out just the part you need without creating extra columns.

Prefer extracting over splitting when you only need one piece. Fewer columns means a smaller model.

7. Unpivot cross-tab data

This is the step that changes how people think about data. Budgets and sales targets usually arrive with months across the top: Product, Apr, May, Jun, and so on. That's readable for humans and terrible for Power BI, because each month is a separate column and you can't filter by "month".

Select the columns that should stay (Product) and choose Unpivot Other Columns. You get three columns: Product, Attribute (the month) and Value. Rename them, set types, and now months are rows you can relate to a date table. Use Unpivot Other Columns rather than Unpivot Columns, so next year's new month columns get unpivoted automatically. Microsoft's article on unpivoting columns shows the difference.

8. Fill down missing values

Many reports print a category once and leave the rows below it blank until the next category. Select the column and use Fill, then Down. Power Query copies the last non-null value into the empty cells below.

Check first that the blanks really are null. Empty strings don't fill. If they're empty text, use Replace Values to turn "" into null, then fill down.

9. Merge and append queries correctly

These two are easy to mix up:

• Append stacks tables on top of each other. Use it when you have the same columns in several files, such as one sales file per month or per branch. Better still, use Get Data, then Folder, and combine every file in the folder so new files are picked up on refresh.
• Merge joins tables side by side on a key, like a VLOOKUP. Use it sparingly. In Power BI it's usually better to keep lookup tables separate and create a relationship in the model, which leads to a cleaner star schema.

When you do merge, pick the join kind on purpose:

Join kind	Keeps	Use it to
Left outer	All rows from the first table, matches from the second	Add a column from a lookup table
Inner	Only rows that match in both	Keep only records that exist in both systems
Left anti	Rows in the first table with no match	Find product codes missing from the master list
Right anti	Rows in the second table with no match	Find master items that never sold
Full outer	Everything from both tables	Reconcile two lists side by side

Left anti is the one I wish more people knew. It turns "why do some sales show as (Blank) in my report?" into a list of exact codes you can send back to whoever maintains the master data.

10. Handle errors and nulls deliberately

Don't just click Remove Errors and move on. First right-click the column and choose Keep Errors in a duplicate query, so you can see what broke. Often it's one bad date format or a "N/A" in a number column. Fix the cause (Replace Values, a conditional column, or a type change using locale), and only then remove or replace what's left.

For nulls in numeric columns, decide whether null means zero or means unknown. Those are different answers, and replacing every null with 0 can make an average look much lower than reality.

What does a finished cleaning query look like?

Here's a short but complete query for a branch sales CSV, so you can see several of the steps together in M. Every line is a step you'd create by clicking; the code is just what Power Query writes for you.

let
    Source = Csv.Document(
        File.Contents("C:\Data\branch_sales.csv"),
        [Delimiter = ",", Encoding = 65001]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    RemovedTotals = Table.SelectRows(
        PromotedHeaders,
        each [Product] <> null and not Text.Contains([Product], "Total")
    ),
    ChangedTypes = Table.TransformColumnTypes(
        RemovedTotals,
        {{"InvoiceDate", type date}, {"Qty", Int64.Type}, {"Amount", type number}},
        "en-IN"
    ),
    CleanedCity = Table.TransformColumns(
        ChangedTypes,
        {{"City", each Text.Proper(Text.Trim(_)), type text}}
    )
in
    CleanedCity

The third argument to Table.TransformColumnTypes, "en-IN", is the "Using Locale" choice from step 2. It's what makes 03/04/2026 read as 3 April rather than 4 March.

Two habits that keep queries fast and readable

Name your steps. Right-click a step and rename it: "Removed total rows" is far more useful than "Filtered Rows 3" when you open the file in six months.

Reference, don't duplicate, when you branch. Create a staging query that connects and cleans the source, disable its load, and reference it for your fact and dimension tables. Duplicating copies all the steps, so a fix in one copy doesn't reach the other. This is exactly the "reference or duplicate queries" point in the PL-300 outline. My PL-300 exam guide lists the full "Prepare the data" section.

How to practise these steps

Find a messy public file, such as a government open data CSV or an old sales export from work (with permission and personal data removed), and set yourself a target: one clean fact table and two dimension tables, with every step renamed. Then change the source file and refresh. If nothing breaks, you've built it properly.

Several of the projects in Power BI projects for beginners start with exactly this kind of data. If you're still working out whether you need Power BI at all, my Power BI vs Excel comparison might help, since Excel has Power Query built in too. And once your data is clean, the next skill is measures: see DAX CALCULATE explained with examples.

In our Power BI training in Anand and live online, the first module covers connecting to sources and cleaning data, and students bring their own messy files where they can. If you'd rather study alone, the self-paced Power BI course includes Power Query lessons with labs and sample datasets.

FAQ

What is Power Query used for in Power BI?

Power Query connects to data sources and cleans and reshapes the data before it loads into the model. Every action is saved as a step and repeated automatically on each refresh.

Is Power Query the same in Excel and Power BI?

They use the same M language and very similar transformations. Some connectors and options differ, but the skills transfer directly between the two.

Should I clean data in Power Query or with DAX?

Clean and reshape in Power Query wherever you can. Use DAX for calculations and business logic. Fixing data with DAX calculated columns makes the model bigger and the formulas harder to follow.

What is the difference between merge and append in Power Query?

Append stacks rows from tables with the same columns. Merge joins two tables side by side on a matching key, like a lookup.

Why are my dates wrong after loading into Power BI?

Usually a locale mismatch: DD/MM dates read as MM/DD. Change the column type using locale and choose the correct region, such as English (India).