<!-- https://learnsql.cpetoday.com/analyze/power-query-editor -->
# Power Query editor: clean data without code

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](/analyze/power-query-accounting-data-prep), and the language itself is in [M basics](/analyze/power-query-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.

> Diagram: A simplified mockup of the Power Query editor window: a ribbon on top, a formula bar, a queries pane on the left, a preview grid in the middle and an Applied Steps pane on the right.

## 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.

```powerquery
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.

```powerquery
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:

```powerquery
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.

```powerquery
Table.Skip(Source, 4)
```

Blank rows are removed with a filter. This is the M the editor writes for **Remove Blank Rows**:

```powerquery
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.

```powerquery
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.

```powerquery
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.

```powerquery
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.

> Diagram: A report-style export where the account group name appears only on the first row of each group. After Fill Down, every row carries its group name.

```powerquery
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.

> Diagram: A wide table with one column per month becomes a long table with one row per account and month. The wide table has three accounts and three months.

```powerquery
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.

```powerquery
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:

```powerquery
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.

> Diagram: Two operations. Append stacks three monthly exports into one table with the same columns. Merge joins a ledger table and a chart of accounts on the account number to add the account name.

```powerquery
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.

```powerquery
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.

```powerquery
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](/analyze/audit-analytics-sql) 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.

```powerquery
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](/analyze/power-query-accounting-data-prep).
- For the language behind the editor, read [M basics](/analyze/power-query-m-basics), then [M tables](/analyze/power-query-m-tables) for the table functions shown above.
- For patterns that scale to large files, read [M patterns](/analyze/power-query-m-patterns).
- To use the cleaned data in a report, go to [Power BI for finance teams](/analyze/power-bi-for-finance).
