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
- 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.
Use the Targets sheet to practice unpivoting monthly columns in Power Query.
- summit-sales-power-bi.xlsx · Targets sheet, with months stored as columns
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.
Getting data
Under Home › Get data you'll find more than a hundred connectors. The ones you'll use most:
| Source | When to use it |
|---|---|
| Excel workbook | Data in sheets or Excel tables (named tables work best) |
| Text/CSV | Exports from accounting software, ERPs, Shopify and other online stores |
| Folder | One file per month or per branch with the same layout: it stacks them all |
| SharePoint / OneDrive | Shared company files in the cloud (they make scheduled refresh easy) |
| SQL Server and other databases | Company systems |
| Web | Tables 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
| Area | What 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. |
| Ribbon | The 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:
- Home › Remove RowsRemove Top RowsDelete the 2 title rows.
- HomeUse First Row as HeadersThe column names become Date, Customer, Product Code…
- Filter the Date columnRemove the TOTAL rowNever load totals calculated by the source system: Power BI will calculate them.
- 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.
- Transform › FormatTrimOn Customer: removes leading and trailing spaces.
- Transform › FormatCapitalize Each WordOn City: "CHICAGO" and "atlanta" become "Chicago" and "Atlanta".
- Double-click the headerRename columnsClear names without spaces: Product Code → ProductCode, Qty → Quantity.
- Home › Remove ColumnsRemove columns you don't needEvery column uses memory and slows the model down.
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?
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:
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Rice | 1,200 | 1,350 | 1,100 |
| Oil | 800 | 760 | 910 |
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.
| Product | Month | Target |
|---|---|---|
| Rice | Jan | 1,200 |
| Rice | Feb | 1,350 |
| Rice | Mar | 1,100 |
| Oil | Jan | 800 |
| … | … | … |
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:
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?
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
- What it does
- Stacks the rows of tables that share the same columns.
- Like
- Copying and pasting rows.
- Example
- Sales 2025 + Sales 2026.
- 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.
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?
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?
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:
- Clean the CSV with the 8 steps in this lesson.
- Unpivot the targets table.
- Rename every applied step with a descriptive name.
- 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.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.