Step-by-Step Power Query Tutorial for Beginners
· 7 min read
Power Query is the data preparation tool built into Power BI and Excel. You connect to a source (a CSV, a database, a folder of files), clean it in the Power Query editor by clicking through menus, and every change is saved as an applied step. On each refresh Power Query replays those steps on the new data, so you clean once and never again by hand. This Power Query tutorial walks through a real cleaning job on a messy Brightline Supply Co. customer export, step by step.
let
Source = Csv.Document(File.Contents("C:\data\customers.csv"), [Delimiter = ",", Encoding = 65001]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"customer_id", Int64.Type}})
in
#"Changed Type"That is what the first three applied steps look like in M, the language Power Query writes for you. You will rarely type M as a beginner, but you should be able to read it.
Open the Power Query editor
- Power BI Desktop: Home > Get data to connect to a source, then Transform data to open the editor. Use Transform data again later to reopen it.
- Excel: Data > Get Data, pick the source, then Transform Data in the preview window.
The editor has four areas you will use all the time:
| Area | What it does |
|---|---|
| Queries pane (left) | One query per table you load |
| Data preview (centre) | The table after the selected step |
| Query Settings > Applied Steps (right) | Every change, in order. Click a step to see the data at that point |
| Formula bar (View > Formula Bar) | The M code of the selected step |
Turn on View > Column quality and Column distribution before you start. They show the share of valid, error and empty values and the number of distinct values per column. By default they are based on the first 1,000 rows; you can switch to the whole dataset in the status bar at the bottom.
The messy file
Brightline's CRM export of customers arrives like this:
| customer_id | customer_name | segment | region |
|---|---|---|---|
| 1001 | Harbor & Pine Hotels | Enterprise | west |
| 1002 | Lakeside Studios␣ | SMB | East |
| 1003 | Pioneer Studios | Mid-Market | ␣North |
| 1004 | River Print Co | SMB | WEST |
| 1002 | Lakeside Studios | SMB | East |
(␣ marks a stray space.) Four problems: inconsistent region spelling, stray spaces, a duplicated customer, and an empty last row. Column distribution on region shows 4 distinct values where there should be 3, because Power Query compares text exactly: "west" and "WEST" are different values to it.
Why it matters: this table becomes the Customers dimension. If customer 1002 appears twice, the one-to-many relationship to Sales cannot be created, and if the duplicate shows up in a later refresh, the refresh fails. More on that in relationships and star schema.
Clean it step by step
Step 1: Promote headers and set types
Power BI usually does both for you when it opens a CSV. Check them anyway in Applied Steps: Promoted Headers and Changed Type. Set customer_id to Whole number and the other three columns to Text. Wrong types are the most common reason a relationship or a sum fails later.
Step 2: Trim the text columns
Select customer_name, segment and region, then Transform > Format > Trim. This removes spaces at the start and end. " North" becomes "North", and "Lakeside Studios " becomes "Lakeside Studios".
Step 3: Fix the case of region only
Select only region, then Transform > Format > Capitalize Each Word. "west" and "WEST" both become "West". Do not apply it to segment, because "SMB" would become "Smb". More on that in the mistakes section.
Step 4: Remove the empty row
Home > Remove Rows > Remove Blank Rows. The table goes from 6 rows to 5.
Step 5: Remove duplicates on the key
Select customer_id only, then Home > Remove Rows > Remove Duplicates. The table goes from 5 rows to 4. Removing duplicates on the key column, not on all columns, is the safe default for a dimension: it guarantees a unique key whatever else differs.
Step 6: Rename and load
Rename the columns to CustomerID, CustomerName, Segment and Region (double-click a header), rename the query to Customers, then Home > Close & Apply (Close & Load in Excel).
The result:
| CustomerID | CustomerName | Segment | Region |
|---|---|---|---|
| 1001 | Harbor & Pine Hotels | Enterprise | West |
| 1002 | Lakeside Studios | SMB | East |
| 1003 | Pioneer Studios | Mid-Market | North |
| 1004 | River Print Co | SMB | West |
Region now has 3 distinct values, and the key is unique. With this table related to Sales, completed revenue by region comes out as West 388.10, East 149.00 and North 75.00, which adds up to Brightline's 612.10.
Read the applied steps
Open Home > Advanced Editor to see every step as M:
let
Source = Csv.Document(File.Contents("C:\data\customers.csv"), [Delimiter = ",", Encoding = 65001]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {
{"customer_id", Int64.Type}, {"customer_name", type text},
{"segment", type text}, {"region", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type", {
{"customer_name", Text.Trim, type text}, {"segment", Text.Trim, type text},
{"region", Text.Trim, type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text", {{"region", Text.Proper, type text}}),
#"Removed Blank Rows" = Table.SelectRows(#"Capitalized Each Word",
each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Duplicates" = Table.Distinct(#"Removed Blank Rows", {"customer_id"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates", {
{"customer_id", "CustomerID"}, {"customer_name", "CustomerName"},
{"segment", "Segment"}, {"region", "Region"}})
in
#"Renamed Columns"Each line is one applied step. Each step takes the previous step's name as its input, which is why the order matters. Things you can do in the Applied Steps pane:
- Click a step to see the data at that point. This is how you debug: walk down the steps until the data looks wrong.
- Gear icon next to a step reopens its dialog, so you can change a setting without starting again.
- Right-click > Rename to give steps names that explain the why ("Keep completed orders only").
- Delete a step, but check the steps after it: if a later step refers to a column the deleted step created, it errors.
Common Power Query steps and their SQL equivalent
If you know SQL, most of the editor maps to something familiar:
| Power Query | SQL |
|---|---|
| Choose Columns / Remove Columns | the SELECT list |
| Filter rows (column header arrow) | WHERE |
| Group By | GROUP BY |
| Merge Queries | JOIN (left outer by default) |
| Append Queries | UNION ALL |
| Remove Duplicates | DISTINCT, or keeping one row per key |
| Add Column > Custom Column | a calculated expression in SELECT |
See how to join two tables in SQL for the join side. Where to put a calculation, in Power Query or in DAX, is a separate decision covered in DAX vs Power Query.
Common mistakes
An AI assistant cleans every text column the same way
Ask an AI assistant to "clean up the customer table in Power Query" and you often get one step that trims and capitalizes all text columns at once. It runs without errors. But Text.Proper turns "SMB" into "Smb" and would turn a size code like "XL" into "Xl" and a name like "McKenzie" into "Mckenzie". DAX compares text without case, so a measure may still work, but Power Query merges are case-sensitive, so a later merge with a segment table keyed "SMB" finds no match. How to spot it: after any bulk text step, look at Column distribution for each column and compare a few values with the source.
Removing duplicates before trimming
If Remove Duplicates runs on all columns before Trim, "Lakeside Studios " and "Lakeside Studios" are different rows, so both survive and the key is still duplicated. Clean first, then deduplicate, and deduplicate on the key.
Filtering out rows you will need later
Removing cancelled orders in Power Query makes them disappear from the model for good. Keep them and filter in a measure, so you can still report on cancellations.
Hard-coded file paths
File.Contents("C:\data\customers.csv") only works on your machine. When someone else refreshes the file, the Source step fails. Use a parameter for the folder, or a shared location.
FAQ
Is Power Query hard to learn for beginners?
No. Most cleaning tasks are menu clicks, and the editor writes the M code for you. Start with types, trim, filter, remove duplicates, merge and group by. That covers most beginner work.
Where are the applied steps in Power Query?
In the Query Settings pane on the right of the Power Query editor. If the pane is hidden, turn it on from the View tab.
What is the difference between Power Query and Power Pivot?
Power Query gets and cleans data before it is loaded. Power Pivot (in Excel) and the model in Power BI hold the loaded tables, the relationships and the DAX measures.
Does Power Query change my source file?
No. It reads the source and applies the steps to a copy in memory. The original CSV or database table stays as it was.
Can I use Power Query in Excel and Power BI the same way?
Mostly yes. The editor, the applied steps and the M language are the same. The difference is where the result goes: a worksheet or the Excel data model, or the Power BI model.
Practise this on a realistic dataset
Write DAX measures on a realistic company model in your browser, and get them checked against the true numbers.
Start practising DAX →