Downloads

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.

Power Query Editor, sample bank export Use First Row as Headers Data Type: Date Remove Top Rows Split Column = Table.PromoteHeaders(Source, [PromoteAllScalars = true]) Queries Bank export Trial balance Sales report Date Description Debit Credit 03/02Coffee shop4.75 03/03Payroll2,100.00 03/05Customer ACH5,000.00 03/06Utilities312.40 03/07Bank fee12.00 Applied Steps Source Promoted Headers Changed Type Filtered Rows Split Column
The Power Query editor window, simplified. Ribbon at the top, formula bar under it, queries on the left, the preview grid in the middle, and Applied Steps on the right. This is an illustration, not a screenshot.

The window, piece by piece

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}})
Watch out

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")
Watch out

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.

Before: blanks under each group Group Account Cash Operating Savings Reserve Revenue Gifts Fill Down After: every row has its group Group Account Cash Operating Cash Savings Cash Reserve Revenue Gifts Remove the subtotal rows before you fill down, or a "Total" label is copied into the blanks below it.
Fill Down. Report exports put the group name on the first row only. Fill Down copies each name into the blank cells beneath it. Remove subtotal rows first, or the subtotal label is copied too.
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.

Wide: one column per month Account Jan Feb Mar Rent 1,200 1,200 1,200 Software 480 510 480 Travel 0 320 75 Unpivot the month columns Long: one row per account and month Account Month Amount Rent Jan 1,200 Rent Feb 1,200 Nine rows in all. Rows 4 to 9 follow the same pattern.
Unpivot. Months across the top become rows, so each row holds one account, one month and one amount. Totals then sum down one column, and a pivot table can rebuild any layout.
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.

Append: stack the monthly exports January export Same columns each month February export Same columns each month March export Same columns each month One table, all three months Add a Month column to keep the source Merge: look up the account name Ledger lines Account no, Amount Chart of accounts Account no, Account name Account no, Amount, Account name
Append stacks tables with the same columns, one under another. Merge joins two tables on a shared key, the way a lookup does.
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

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.