Lesson 2 of 16 · Level 1: Beginner

Power Query in Power BI: How to Clean and Transform Data

  • ⏱ 13 min read
  • ❓ 4 review questions
  • ✍️ Exercise with solution
  • 📥 Downloadable template
In this lesson
  1. Getting data
  2. The Power Query Editor
  3. The essential transformations
  4. Unpivoting: from a "wide" table to a "long" table
  5. Combining queries: Append and Merge
  6. Other tools worth knowing
  7. Profile your data before you trust it
  8. Common Power Query mistakes
  9. Summary
What you'll learn
  • Connect Power BI to Excel, CSV files, folders and other sources.
  • Clean data in the Power Query Editor without touching the original file.
  • Unpivot "wide" tables and combine queries with Append and Merge.
  • Use Applied Steps so your cleanup repeats itself every month.
  • Profile data quality and read the M code behind your queries.
📥 Practice this lesson

Use the Targets sheet to practice unpivoting monthly columns in Power Query.

Real-world data almost never arrives ready to use: title rows above the table, dates stored as text, extra spaces, months spread across columns, one file per month. Power Query is the Power BI editor for cleaning and shaping data. Its superpower is that it records every step you take and replays it automatically each time you refresh. You clean once; it stays clean forever.

8 → 96rows in Summit's Targets sheet after unpivoting (8 rows × 12 months)
100+connectors in Get data: Excel, CSV, folders, databases, the web…
Onceis all you clean; every refresh replays your steps automatically

Getting data

Under Home › Get data you'll find more than a hundred connectors. The ones you'll use most:

SourceWhen to use it
Excel workbookData in sheets or Excel tables (named tables work best)
Text/CSVExports from accounting software, ERPs, Shopify and other online stores
FolderOne file per month or per branch with the same layout: it stacks them all
SharePoint / OneDriveShared company files in the cloud (they make scheduled refresh easy)
SQL Server and other databasesCompany systems
WebTables published on web pages

The preview window offers two buttons: Load (brings the data in as is) and Transform data (opens Power Query). Unless the data is perfect, always choose Transform data.

The Power Query Editor

AreaWhat it's for
Queries pane (left)Every table you import is a query. Give them clear names: Sales, Products, Customers.
Preview (center)The first rows of the query. The icon in each column header shows its data type.
Applied Steps (right)Your transformations, in order. Click any step to see the data at that point, rename it or delete it.
RibbonThe Home, Transform and Add Column tabs hold all the tools.

When you're done, click Close & Apply and the clean data loads into the model. The source file is never changed.

The essential transformations

Suppose Summit's order system exports a CSV that looks like this:

Sales report - Generated 11/05/2026

Date,Customer,Product Code,City,Qty,Amount
10/01/2026,  Main Street Convenience ,P-001,CHICAGO,10,125.50
10/01/2026,Sunrise Market,P-004,atlanta,5,60.00
…
TOTAL,,,,,48250.00

Here's how to clean it:

  1. Home › Remove RowsRemove Top RowsDelete the 2 title rows.
  2. HomeUse First Row as HeadersThe column names become Date, Customer, Product Code…
  3. Filter the Date columnRemove the TOTAL rowNever load totals calculated by the source system: Power BI will calculate them.
  4. Right-click › Change TypeSet each column's data typeDate → Date, Qty → Whole Number, Amount → Decimal Number. If a file uses day/month dates (common from Canadian or European suppliers), use Change Type › Using Locale and pick the source country.
  5. Transform › FormatTrimOn Customer: removes leading and trailing spaces.
  6. Transform › FormatCapitalize Each WordOn City: "CHICAGO" and "atlanta" become "Chicago" and "Atlanta".
  7. Double-click the headerRename columnsClear names without spaces: Product Code → ProductCode, Qty → Quantity.
  8. Home › Remove ColumnsRemove columns you don't needEvery column uses memory and slows the model down.
The automatic "Changed Type" step

Power Query detects types on import and adds a step for it. Always review it: it sometimes turns codes like "00125" into the number 125 or misreads dates. The right data type is the foundation of correct calculations.

1. The file your system exports ends with a "TOTAL" row. What do you do in Power Query?

  • Keep it: the total is already calculated.
  • Delete it by hand in the CSV every month.
  • Remove it with a step (for example, filtering out that row), because Power BI calculates totals itself.

If you load it, the total gets counted twice. Removing it in Power Query means the step repeats on every refresh.

Unpivoting: from a "wide" table to a "long" table

Many Excel files put months in columns. That's easy to read but terrible for analysis:

ProductJanFebMar
Rice1,2001,3501,100
Oil800760910

Select the Product column, right-click and choose Unpivot Other Columns. You get a long table with three columns: Product, Attribute (the month) and Value. Rename them Product, Month and Target. Now you can filter, relate and chart by month, and if next year's file adds a column, it flows in automatically.

ProductMonthTarget
RiceJan1,200
RiceFeb1,350
RiceMar1,100
OilJan800
………

Practice with Summit's Targets sheet

The Targets sheet in the course file has 8 rows (4 reps × 2 years) and the columns RepID, Year and Jan through Dec. Select RepID and Year (hold Ctrl), right-click › Unpivot Other Columns, and rename Attribute to Month and Value to Target. You'll get 96 rows. To relate the table to a calendar later (Lesson 8), add a column with the first day of each month. Here's the full query in the Advanced Editor:

let
    Source = Excel.Workbook(File.Contents("C:\Data\summit-sales-power-bi.xlsx"), null, true),
    Targets = Source{[Item="Targets", Kind="Table"]}[Data],
    Unpivoted = Table.UnpivotOtherColumns(Targets, {"RepID", "Year"}, "Month", "Target"),
    Months = {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},
    WithDate = Table.AddColumn(Unpivoted, "Date",
        each #date([Year], List.PositionOf(Months, [Month]) + 1, 1), type date),
    Types = Table.TransformColumnTypes(WithDate, {{"Target", Int64.Type}})
in
    Types

List.PositionOf returns a zero-based position ("Jan" = 0), which is why we add 1. Check your result: targets add up to $251,550 for 2025 and $275,550 for 2026. Here's how the 2026 target is split among the reps:

2026 target by sales rep (% of the $275,550 total)

Emily Carter (R1)28.2%
Daniel Ortiz (R4)25.7%
Marcus Johnson (R2)23.1%
Rachel Kim (R3)22.9%

In dollars: Emily $77,775, Daniel $70,950, Marcus $63,675 and Rachel $63,150. With the original wide table, this chart would have required adding up 12 columns by hand.

2. The Targets sheet has 8 rows with RepID, Year and 12 month columns. After unpivoting the months, how many rows will it have?

  • 12
  • 96
  • 104

Each original row becomes 12 rows, one per month: 8 × 12 = 96. The columns drop from 14 to 4 (RepID, Year, Month and Target).

Combining queries: Append and Merge

Append QueriesOne table under another
What it does
Stacks the rows of tables that share the same columns.
Like
Copying and pasting rows.
Example
Sales 2025 + Sales 2026.
More rows
Merge QueriesColumns from another table
What it does
Adds columns from another table based on a matching column.
Like
VLOOKUP or XLOOKUP.
Example
Bringing each product's Category into every sale.
More columns

In Power BI, rather than merging everything into one giant table, it's usually better to load tables separately and relate them in the model (Lesson 5). Use Merge when you truly need the column inside the table.

A folder with one file per month

If you receive sales_2026_01.csv, sales_2026_02.csv and so on, save them in one folder and use Get data › Folder › Combine & Transform Data. Power Query builds a function that applies your cleanup steps to every file and stacks the results. Next month, drop the new file into the folder and click Refresh.

3. You have a targets table with one column per month (Jan, Feb, Mar…). Which transformation prepares it for analysis?

  • Unpivot the month columns.
  • Transpose.
  • Merge Queries.

Unpivoting turns month columns into rows, with one Month column and one value column.

Other tools worth knowing

  • Split Column by delimiter: "Chicago - IL" → City and State.
  • Replace Values: change "Chcago" to "Chicago".
  • Fill Down: fill blank cells with the value above (typical of reports with merged cells).
  • Conditional Column: "If Amount ≥ 1,000 then 'Large', else 'Standard'", without writing code.
  • Column From Examples: type the result you want in two or three rows and Power Query figures out the transformation.
  • Group By: summarize before loading (handy with huge data).
  • Enable load (right-click a query to turn it off): for helper queries you don't need in the model.

4. Next month a new file arrives with the same layout. What do you need to do to include it in the report?

  • Repeat every cleanup step.
  • Put it in the folder or data source and click Refresh: the Applied Steps replay on their own.
  • Build a new report.

Power Query records your steps and reruns them against the new data on every refresh.

Profile your data before you trust it

On the View tab of the Power Query Editor, turn on three options that show the health of every column without writing anything:

  • Column quality: percentage of valid, error and empty values.
  • Column distribution: how many distinct and unique values there are.
  • Column profile: statistics (min, max, average) and value frequencies for the selected column.

By default, profiling uses the first 1,000 rows. Click the text in the status bar at the bottom and switch to profiling the entire data set when you need certainty. Try it on Summit's Sales[ProductID]: you'll see 13 distinct values, even though the Products table only has 12. The extra one is code P99, which you'll investigate in Lesson 5. Profiling warns you before the problem shows up as a "(Blank)" row in a report.

Common Power Query mistakes

Fixing data by hand in the source Excel file every month.

Make every fix a Power Query step so it repeats on each refresh.

Removing the TOTAL row with "Remove Bottom Rows" when the file ends with a variable number of notes.

Filter by content (Date is not "TOTAL"): it works no matter how many rows there are.

Accepting the automatic "Changed Type" step without looking.

Check every type: codes as text, dates with the right locale, amounts as decimals.

Leaving steps named "Custom1" or "Removed Columns2".

Rename them ("NoTotalRow", "CityFixed") so you'll understand the query in six months.

Merging every table into one huge table "just in case".

Load separate tables and relate them in the model; merge only when you need the column.

Practice exercise: clean a real-world export

Create a CSV in the messy format above (two title rows, extra spaces, cities in mixed case, a TOTAL row) and a targets table with months in columns. Then:

  1. Clean the CSV with the 8 steps in this lesson.
  2. Unpivot the targets table.
  3. Rename every applied step with a descriptive name.
  4. Add a new row to the CSV and confirm it appears, clean, after a refresh.
See solution

Every click is written in a language called M. Open it with Home › Advanced Editor. You don't need to write it, but reading it helps you understand what the query does:

let
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"), [Delimiter=",", Encoding=65001]),
    NoTitle = Table.Skip(Source, 2),
    Headers = Table.PromoteHeaders(NoTitle, [PromoteAllScalars=true]),
    NoTotalRow = Table.SelectRows(Headers, each [Date] <> "TOTAL"),
    Types = Table.TransformColumnTypes(NoTotalRow,
        {{"Date", type date}, {"Qty", Int64.Type}, {"Amount", type number}}, "en-US"),
    Trimmed = Table.TransformColumns(Types, {{"Customer", Text.Trim, type text}}),
    CityFixed = Table.TransformColumns(Trimmed, {{"City", Text.Proper, type text}})
in
    CityFixed

Notice that the TOTAL row is removed by filtering on its content, so the query keeps working when the number of rows changes. After you add a row and refresh, it should show up with trimmed text and a proper-case city.

Summary

  • Choose "Transform data" to clean in Power Query before loading.
  • Remove title and total rows, set correct types, trim text and rename columns.
  • Unpivot tables that store months as columns: Summit's 8 target rows become 96.
  • Append stacks rows; Merge adds columns, though relating tables in the model is usually better.
  • Applied Steps replay on every refresh: clean once, stay clean.
  • Profile columns (quality, distribution, profile) to catch orphan codes and errors before they load.

Next lesson: Power BI Visuals, Filters and Slicers. You'll turn clean data into clear charts: which visual to use when, plus filters and slicers.

🎓 Save your progress and get a free certificate

Create your account in 30 seconds, mark your lessons and download your certificate at the end.

Enroll for free