Power Query editor: clean data without code
The editor, applied steps, the common clicks for finance data, and what each click writes in M.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Find your way around the Power Query editor and its applied steps.
- Clean and reshape finance data with the common clicks, without writing code.
- Read the M code that each click writes, and keep your steps readable.
- Recognize the three kinds of file the editor works with.
Most finance data arrives messy. A bank export has a title block above the header. A trial balance has group names that appear once and subtotals in the middle. A monthly report runs the months across the top. You can fix all of this by hand, but you will redo it every month. Power Query records each fix as a step. Next month you refresh, and every step runs again.
This guide is for people who use Excel or Power BI and have never opened the editor. It does not teach M, the language behind it. You can use the editor for a long time without writing a line of M. Each click below shows the M it writes, so you can read it when you need to. The first guide in this track is this one. The accounting recipes are in Power Query for accounting data, and the language itself is in M basics.
Where the editor lives
In Excel: on the Data tab, choose Get Data, pick a source such as From File > From Workbook or From Table/Range, then Transform Data. The editor opens on that data.
In Power BI Desktop: on the Home tab, choose Transform data.
Either way, the editor opens on a copy of the data. Your original file is not changed until you load the result back.
The window, piece by piece
- Ribbon. The tabs (Home, Transform, Add Column, View) hold every click in this guide. Most clicks are on Transform.
- Formula bar. Shows the M for the step you have selected. You can edit it here, but you do not have to.
- Queries pane. The list of queries on the left. Each one is a separate table. A bank export, a trial balance and a sales report are three queries, and they can feed each other.
- Preview grid. The middle. It shows the result of the selected step, a few hundred rows at a time.
- Applied Steps. The list on the right. Each click adds one step, in order. Select a step to see the table at that point. Delete or rename a step to change the chain.
Applied Steps is the most important pane. Read it from top to bottom to see exactly how the data was cleaned.
Three files you will meet
The examples use three files. They are made-up examples in the shape of real exports, not files from the sample databases. Each has a problem that the editor can fix.
A bank export (CSV). Four lines of account information sit above the header. Dates are text.
| Row | Content |
|---|---|
| 1 to 4 | Account name, account number, period, blank line |
| 5 | Date, Description, Debit, Credit, Balance |
| 6 onward | 03/02/2026, Coffee shop, 4.75, (blank), 1,240.10 |
A trial balance (Excel). The group name is typed once, in the first row of each group. Subtotal rows say "Total Cash". A blank row separates the sections.
| Group | Account No | Account | Debit | Credit |
|---|---|---|---|---|
| Cash | 1000 | Operating | 272,404.68 | |
| 1010 | Savings | 900,000.00 | ||
| Total Cash | 1,172,404.68 | |||
| Revenue | 4000 | Gifts | 900,000.00 |
A monthly sales report (Excel). One row per product line, one column per month, and a total column at the end.
| Product line | Jan | Feb | Mar | Total |
|---|---|---|---|---|
| Consulting | 12,400 | 13,100 | 11,900 | 37,400 |
| Training | 4,200 | 3,800 | 5,100 | 13,100 |
The 14 clicks
Each click below has four parts: what it does, when to use it, the M it writes, and what to watch for. Some clicks are paired, because you use them together.
1. Promote headers
What it does. Turns the first row into column names.
When. The export has a header row that Excel or the file reader did not recognize, or the first row holds the names.
Table.PromoteHeaders(Source, [PromoteAllScalars = true])
Watch for. Promote after you remove the title block. If the title lines are still in place, the editor promotes the first title line as the header.
2. Change type, and set the locale
What it does. Tells the editor what each column holds: date, number or text.
When. Always. A date stored as text cannot be sorted or filtered by month, and a number stored as text will not add up. Set the type on every column you calculate with.
Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}})
A date of 03/04/2026 is March 4 in the US and April 3 in the UK. A decimal comma in a German file (1.234,56) reads as the wrong number unless the editor knows the locale.
Use Data Type > Using Locale on the Transform tab and pick the file's locale. The M adds a culture argument:
Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")
The editor guesses the type from the first rows. If a later row breaks the guess, you get an error in that cell.
Check the column for errors after you set the type.
3. Remove top rows and blank rows
What it does. Drops the title block, and drops the empty rows between sections.
When. Every report export has some of each.
Table.Skip(Source, 4)
Blank rows are removed with a filter. This is the M the editor writes for Remove Blank Rows:
Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
Watch for. Remove the top rows before you promote headers. Count the title lines by looking at the preview, not from memory.
4. Split column
What it does. Splits one column into two or more, on a separator.
When. A bank description reads "Vendor name - Invoice 1042". You want the vendor and the memo in separate columns.
Table.SplitColumn(Source, "Description", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Vendor", "Memo"})
Watch for. Split on a separator the text uses every time. If one row has two hyphens, the split puts the second part in a third column. Check the number of new columns.
5. Trim and clean text
What it does. Removes leading and trailing spaces, and non-printing characters that come from copying out of other systems.
When. Vendor names look the same in the preview but do not match when you join or group. The usual cause is a trailing space.
Table.TransformColumns(Source, {{"Vendor", each Text.Trim(Text.Clean(_)), type text}})
Watch for. Trim fixes spaces, not spelling. "Larson Flooring" and "Larson Flooring Inc" stay different until you rename one of them.
6. Replace values
What it does. Swaps one value for another, in one column or all of them.
When. The export uses a dash for zero, or "N/A" for a blank.
Table.ReplaceValue(Source, "-", null, Replacer.ReplaceValue, {"Amount"})
Watch for. Replacing a value in an amount column with null makes the column blank, not zero. If you want zero, replace with 0 and set the type afterward.
7. Fill down
What it does. Copies a value down into the blank cells below it, until the next value appears.
When. The trial balance has the group name only in the first row of each group. Without fill down, the other rows have no group, and a group total will leave them out.
Table.FillDown(Source, {"Group"})
Watch for. Remove the subtotal rows before you fill down. A "Total Cash" row is copied into every blank row below it, so the group label becomes wrong. Remove the subtotals, then fill, then check the group totals against the report.
8. Unpivot columns
What it does. Turns columns into rows. The month columns become one month column and one amount column.
When. The monthly sales report has one column per month. A pivot table, a chart and a DAX measure all want one row per month. Unpivot first.
Table.UnpivotOtherColumns(Source, {"Product line"}, "Month", "Amount")
The Total column comes through too. Remove it before you unpivot. If you leave it in, the total becomes a fourth month, and summing the Amount column counts every sale twice.
Watch for. After unpivot, the Amount column is text. Set its type with click 2.
9. Merge queries
What it does. Joins two tables on a shared key, like a lookup. It brings a column from one table into the other.
When. The bank export has account numbers, and you want the account names from the chart of accounts. The merge is the lookup.
Table.NestedJoin(Ledger, {"Account No"}, Accounts, {"Account No"}, "Accounts", JoinKind.LeftOuter)
Merge creates a column of tables. Expand it to pull out the column you want:
Table.ExpandTableColumn(Merged, "Accounts", {"Account Name"}, {"Account Name"})
Watch for. Use a left join to keep every ledger row even when no account matches. Then filter for nulls to find the missing accounts. An inner join drops those rows without warning. The join key must have the same type on both sides. A number in one query and text in the other will not match.
10. Append queries
What it does. Stacks tables that have the same columns, one under another.
When. You have one export per month, and you want one table for the year.
Table.Combine({January, February, March})
Watch for. Append matches columns by name. A column called "Amt" in one export and "Amount" in another makes two columns, with blanks in each. Rename before you append.
11. Group by
What it does. Summarizes rows, such as a total by account or a count by vendor.
When. You need a total by account from the detail, for a check against the report.
Table.Group(Source, {"Account No"}, {{"Total", each List.Sum([Amount]), type number}})
Watch for. Group by the key only. If you add a column that is not in the key, the group splits into more rows than you expect.
12. Add conditional column
What it does. Adds a column whose value depends on a test, such as "Review" when an amount is over a limit.
When. You want to flag items for follow-up, such as entries over an approval limit.
Table.AddColumn(Source, "Flag", each if [Amount] > 10000 then "Review" else "OK", type text)
Watch for. The limit is written into the step. When the limit changes, change the step, or use a parameter so it lives in one place. The audit analytics guide uses the same kind of threshold test in SQL.
13. Combine files from a folder
What it does. Reads every file in a folder and stacks them into one table.
When. The bank sends one CSV per month to a folder. You want the whole year in one query, and next month's file should join on refresh.
let
Files = Folder.Files(FolderPath),
CsvOnly = Table.SelectRows(Files, each [Extension] = ".csv"),
Parsed = Table.AddColumn(CsvOnly, "Data", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter = ",", Encoding = 65001]), [PromoteAllScalars = true])),
Expanded = Table.ExpandTableColumn(Parsed, "Data", {"Date", "Account", "Amount"}, {"Date", "Account", "Amount"}),
Typed = Table.TransformColumnTypes(Expanded, {{"Date", type date}, {"Amount", type number}})
in
Typed
FolderPath is a parameter that holds the folder path. Set it once in Manage Parameters, not in each query.
Watch for. Each file must have the same columns. The Expanded step names the columns, so a file with a renamed column causes an error on refresh. That error is the right result: it tells you the export changed.
14. Refresh
What it does. Runs every step again on the current files.
When. Each month, after you save the new file in the folder or the source updates.
In Excel, choose Data > Refresh All. In Power BI Desktop, choose Home > Refresh. The editor's Refresh Preview button reruns the steps without loading the result.
Watch for. A refresh reruns every step, so a step that depends on a fixed row count will break. Filter by content, such as a date or an account, not by position.
Keep the steps readable
Rename each step as you go. Filtered Rows tells you nothing a month later. Removed Title Rows tells you what happened. Right-click a step in Applied Steps and choose Rename.
Do not delete a step in the middle to fix a problem. Change it, or add a step after it. Deleting a middle step can break every step below it, and the error appears in a place far from the cause.
Where to go next
- The recipes for real accounting files, including a bank statement and a trial balance with merged cells, are in Power Query for accounting data.
- For the language behind the editor, read M basics, then M tables for the table functions shown above.
- For patterns that scale to large files, read M patterns.
- To use the cleaned data in a report, go to Power BI for finance teams.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.