<!-- https://learnsql.cpetoday.com/analyze/power-query-accounting-data-prep -->
# Power Query for accounting data

Accounting data arrives in bad shape. A bank export has a title block above the header. A trial balance from Excel has merged cells and subtotal rows. Monthly files have slightly different column names. Vendor names are spelled three ways. This guide works through the real problems that finance teams meet when they load data into Power Query, with a recipe for each one.

Each recipe has the same parts: the problem, the M code, and what to check before you trust the result. The code is written for Power Query in Excel and Power BI. If you are new to M, read [M basics](/analyze/power-query-m-basics) first. The [M tables](/analyze/power-query-m-tables) guide covers the table functions used here.

Some recipes read files you have on your own computer. Those files are not included on LearnSQL, so the file paths are parameters you set. Other recipes read the LearnSQL sample databases. For those, the server is `learnsql.cpetoday.com,1433`. The database name and schema differ by sample:

| Sample database | Database name in `Sql.Database` | Schema in `Source{[Schema = ..., Item = ...]}` |
|---|---|---|
| QuickBooks Desktop style accounting | `qbd` | `QuickBooks` |
| QuickBooks Online style accounting | `qbo` | `QuickBooksOnline` |
| CPA firm practice management | `cpafirm` | `dbo` |
| Nonprofit fund accounting | `nonprofit` | `dbo` |

The schema name matters. `QuickBooks` is not `dbo`. If a `Source` lookup fails with "key not found", check the schema column in the Navigator.

## Set up parameters first

Put the file locations and the reporting period in parameters, so you change them in one place. A parameter is a query that holds one value. Create each one in **Home > Manage Parameters**, or paste this into a blank query and set it as a parameter (the `meta` record marks it as one).

```powerquery
"C:\Finance\Exports\2026" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]
```

Create a second parameter for the first day of the period, as a date:

```powerquery
#date(2026, 7, 1) meta [IsParameterQuery = true, Type = "Date", IsParameterQueryRequired = true]
```

Name the first parameter `FolderPath` and the second `StartPeriod`. Use them in any query like this:

```powerquery
Table.SelectRows(Lines, each [Date] >= StartPeriod and [Date] < Date.AddMonths(StartPeriod, 12))
```

Parameters keep a fiscal year close, the same for every export, from one copy of the query to the next.

## Recipe 1: a bank or credit card export

**The problem.** The bank exports a CSV with four lines of account information above the header. Dates are text in `M/d/yyyy` form. Money is in two columns: `Debit` for money out and `Credit` for money in. Some rows have a blank in one of them. Some negative amounts come as `(1,234.50)`.

**The M.** This query skips the title block, types the date, and makes one signed `Amount` column with money in positive.

```powerquery
let
    Raw = Csv.Document(File.Contents(BankFile), [Delimiter = ",", Encoding = 65001]),
    NoTitle = Table.Skip(Raw, 4),
    Headers = Table.PromoteHeaders(NoTitle, [PromoteAllScalars = true]),
    WithDate = Table.AddColumn(Headers, "Date",
        each Date.FromText([Posted Date], [Format = "M/d/yyyy", Culture = "en-US"]), type date),
    ToAmount = (debit as nullable text, credit as nullable text) as number =>
        (if credit = null or Text.Trim(credit) = "" then 0 else Number.FromText(Text.Remove(credit, {",", "$"}), "en-US"))
        - (if debit = null or Text.Trim(debit) = "" then 0 else Number.FromText(Text.Remove(debit, {",", "$"}), "en-US")),
    WithAmount = Table.AddColumn(WithDate, "Amount", each ToAmount([Debit], [Credit]), type number),
    Result = Table.SelectColumns(WithAmount, {"Date", "Description", "Amount"})
in
    Result
```

`BankFile` is a parameter that holds the path to the CSV. Set it to the full file path, not the folder.

> **Audit tip.** Sum `Amount` and compare it with the closing balance minus opening balance on the statement. The totals must agree to the cent.

**What to check.** Look at the first and last `Date` values to confirm the period. If the dates come through as `null`, the date format in the file is not the one in the `Format` option.

## Recipe 2: a trial balance from Excel

**The problem.** The trial balance is an Excel sheet. The account group name is in a merged cell, so only the first row of each group has a value. Subtotal rows say "Total Cash" and similar. A blank row sits between sections.

**The M.** Read the sheet, promote the header row, fill the account group down into the blank cells, and drop the subtotal and blank rows.

```powerquery
let
    Source = Excel.Workbook(File.Contents(TrialBalanceFile), null, true),
    Sheet = Source{[Item = "Trial Balance", Kind = "Sheet"]}[Data],
    Promoted = Table.PromoteHeaders(Sheet, [PromoteAllScalars = true]),
    Filled = Table.FillDown(Promoted, {"Group"}),
    NoBlanks = Table.SelectRows(Filled, each [Account] <> null and [Account] <> ""),
    NoSubtotals = Table.SelectRows(NoBlanks, each not Text.StartsWith([Account], "Total")),
    Typed = Table.TransformColumnTypes(NoSubtotals, {{"Debit", type number}, {"Credit", type number}}),
    Net = Table.AddColumn(Typed, "Net", each [Debit] - [Credit], type number)
in
    Net
```

`TrialBalanceFile` is a parameter that holds the workbook path. The sheet name `Trial Balance` must match the tab name exactly, including spaces.

**What to check.** The sum of `Net` must be zero. A trial balance that does not net to zero means a row was dropped or a subtotal was kept. Count the rows before and after the filter steps and write the two numbers down, so you can explain the difference to a reviewer.

## Recipe 3: twelve monthly files in one folder

**The problem.** Each month the bookkeeper saves a CSV named like `2026-07.csv`. You want all twelve in one table, with a column that says which file each row came from. The column headers should match in every file, and if one file has a different header, you want to find out.

**The M.** List the files, read each one as a table with its own headers, tag each row with the file name, and combine them. The file list is filtered to CSV files only.

```powerquery
let
    Files = Folder.Files(FolderPath),
    CsvOnly = Table.SelectRows(Files, each Text.EndsWith([Name], ".csv")),
    Tables = List.Transform(Table.ToRecords(CsvOnly), (f) =>
        Table.AddColumn(
            Table.PromoteHeaders(Csv.Document(f[Content], [Delimiter = ",", Encoding = 65001]), [PromoteAllScalars = true]),
            "SourceFile",
            each f[Name],
            type text
        )
    ),
    Combined = Table.Combine(Tables)
in
    Combined
```

**What to check.** Run `Table.ColumnNames` on each file once by hand, or add a step that counts the distinct column lists.

> **Watch out.** If a month has an extra or renamed column, `Table.Combine` produces a wider table with `null` cells for the missing columns, and nothing fails.

Check that the row count per `SourceFile` looks reasonable, and that no month is missing.

## Recipe 4: mapping accounts with a fallback

**The problem.** The accounting system has its own account types. The report needs a statement line for each account, such as Cash, Receivables or Revenue. Some accounts are new and not in the mapping table yet. Those must not disappear from the report, and they must be counted.

**The M.** This uses the chart of accounts in the `qbd` sample. The mapping table is a small table written in the query. In practice it is an Excel sheet that the controller maintains.

```powerquery
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "qbd"),
    Accounts = Source{[Schema = "QuickBooks", Item = "Accounts"]}[Data],
    Chart = Table.SelectColumns(Accounts, {"FullName", "Type", "Number"}),
    Mapping = #table(type table [AccountType = text, StatementLine = text], {
        {"BANK", "Cash"},
        {"ACCOUNTSRECEIVABLE", "Receivables"},
        {"INCOME", "Revenue"},
        {"COSTOFGOODSSOLD", "Cost of sales"},
        {"EXPENSE", "Operating expenses"}
    }),
    Joined = Table.NestedJoin(Chart, {"Type"}, Mapping, {"AccountType"}, "Map", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Map", {"StatementLine"}, {"StatementLine"}),
    WithFallback = Table.TransformColumns(Expanded, {{"StatementLine", each _ ?? "Unmapped", type text}})
in
    WithFallback
```

`JoinKind.LeftOuter` keeps every account, including the ones with no match. The `??` operator replaces a `null` with "Unmapped" when the account type is not in the table.

**What to check.** Count the rows with `StatementLine = "Unmapped"`. Put that count on the report with the other data quality figures (recipe 11). Expect `Type` values such as `FIXEDASSET` and `OTHERCURRENTLIABILITY` to show up as unmapped until someone adds them to the mapping. Never let an unmapped account silently join a total.

## Recipe 5: unpivoting a monthly budget grid

**The problem.** The budget is a grid. Accounts run down the rows, months run across the columns. Reports and the DAX model need one row per account per month.

**The M.** The grid is written in the query so you can test it. Use `Table.UnpivotOtherColumns` to turn every column except the account into rows.

```powerquery
let
    Grid = #table(type table [Account = text, Jan = number, Feb = number, Mar = number], {
        {"4000 Product sales", 120000, 125000, 130000},
        {"6100 Salaries", -80000, -80000, -82000}
    }),
    Long = Table.UnpivotOtherColumns(Grid, {"Account"}, "Month", "Budget"),
    Typed = Table.TransformColumnTypes(Long, {{"Budget", type number}})
in
    Typed
```

`Table.UnpivotOtherColumns(table, {"Account"}, "Month", "Budget")` keeps `Account` as it is and creates two columns: `Month`, which holds the old column names, and `Budget`, which holds the values.

**What to check.** The row count must equal the number of accounts times the number of months. Sum `Budget` by account and compare with the annual budget. The sign convention must match the ledger. In this example revenue is positive and cost is negative in the budget, so flip costs in the model if your ledger stores them as positive debits. See [DAX for financial reporting](/analyze/dax-financial-reporting) for how the signs are handled.

## Recipe 6: vendor names that are almost the same

**The problem.** The same vendor appears as "C.U. Electric", "CU Electric" and "C. U. Electric" in different bills. Spend reports and 1099 totals are wrong until the names are matched.

**The M.** Build a match key that ignores case, punctuation and spaces. Group by the key, and keep any key that has more than one name. This is the same technique for any name list.

```powerquery
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "qbd"),
    Vendors = Source{[Schema = "QuickBooks", Item = "Vendors"]}[Data],
    Keyed = Table.AddColumn(Vendors, "MatchKey",
        each Text.Remove(Text.Lower([Name]), {".", ",", "'", " ", "&", "-"}), type text),
    Groups = Table.Group(Keyed, {"MatchKey"}, {
        {"Count", each Table.RowCount(_), Int64.Type},
        {"Names", each Text.Combine([Name], " | "), type text}
    }),
    Candidates = Table.SelectRows(Groups, each [Count] > 1)
in
    Candidates
```

**What to check.** Read the candidate list. A match key is a search aid, not a decision. "Bayshore Water" and "Bayshore Water Co" may be different vendors with the same key only if the rule is too loose. Choose the vendor master record yourself, then map the other names to it. Do not merge two vendors with different tax IDs, even if the names match. The `qbd` vendors include a `Company` column as well as `Name`; repeat the key on `Company` to find matches that show up in only one of them.

## Recipe 7: splitting "1000 - Cash"

**The problem.** The chart of accounts exports the account number and name in one text column, such as `1000 - Cash`. You need the number as a number, and the name on its own.

**The M.** Use `Text.BeforeDelimiter` and `Text.AfterDelimiter`. They split at the first occurrence of the delimiter.

```powerquery
let
    Accounts = #table(type table [Account = text], {
        {"1000 - Cash"},
        {"1010 - Trust account cash"},
        {"4100 - Tax preparation revenue"}
    }),
    Number = Table.AddColumn(Accounts, "Number", each Text.BeforeDelimiter([Account], " - "), type text),
    Name = Table.AddColumn(Number, "Name", each Text.AfterDelimiter([Account], " - "), type text),
    Typed = Table.TransformColumnTypes(Name, {{"Number", Int64.Type}}),
    Clean = Table.RemoveColumns(Typed, {"Account"})
in
    Clean
```

`Table.SplitColumn` with `Splitter.SplitTextByDelimiter(" - ", QuoteStyle.None)` also works. The `Text.` functions are easier to read when the delimiter may appear more than once in a name.

**What to check.** Every `Number` must convert to an integer. If one row fails, the error will show on the column, and you can find the row with recipe 11. Account numbers with letters, such as `1000A`, need a different rule.

## Recipe 8: text numbers, parentheses and trailing minus

**The problem.** Amounts arrive as text. Negative values come in three forms: `(1,234.50)`, `1,234.50-`, and `-1234.50`. Some have dollar signs and spaces. Excel will not sum these as numbers.

**The M.** Write one function that handles all three forms, and call it on the column. The function is stored as a step, so you can read it.

```powerquery
let
    ToNumber = (raw as nullable text) as nullable number =>
        let
            trimmed = if raw = null then null else Text.Trim(raw),
            cleaned = if trimmed = null or trimmed = "" then null else Text.Remove(trimmed, {"$", ",", " "}),
            isNegative = cleaned <> null and (
                (Text.StartsWith(cleaned, "(") and Text.EndsWith(cleaned, ")"))
                or Text.EndsWith(cleaned, "-")
                or Text.StartsWith(cleaned, "-")
            ),
            digits = if cleaned = null then null else Text.Remove(cleaned, {"(", ")", "-"}),
            value = if digits = null then null else Number.FromText(digits, "en-US"),
            signed = if value = null then null else if isNegative then -value else value
        in
            signed,
    Sample = #table(type table [Amount = text], {
        {"(1,234.50)"}, {"1,234.50-"}, {"-1234.50"}, {"$ 88.00"}, {""}
    }),
    Converted = Table.AddColumn(Sample, "Value", each ToNumber([Amount]), type nullable number)
in
    Converted
```

Empty strings become `null`, not zero. That is deliberate. Turn them into zero only when you know a blank means zero in that file.

**What to check.** The `Value` column for the sample rows should read -1234.5, -1234.5, -1234.5, 88 and blank. Check the sum of a real column against the total printed in the file. A rounding difference of a cent is usually a number that was rounded in the export, not a conversion error.

## Recipe 9: a clean general ledger table

**The problem.** A general ledger export has one row per journal line. The entry date and source document live on the journal entry, and the account name lives on the account. The report needs one table with all of those columns, one row per line, and a check that each entry balances.

**The M.** Read the three tables from the `cpafirm` sample, join them, and calculate a signed amount. The lines table has `debit` and `credit` columns.

```powerquery
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "cpafirm"),
    Lines = Source{[Schema = "dbo", Item = "gl_journal_lines"]}[Data],
    Entries = Source{[Schema = "dbo", Item = "gl_journal_entries"]}[Data],
    Accounts = Source{[Schema = "dbo", Item = "gl_accounts"]}[Data],

    LineCols = Table.SelectColumns(Lines, {"journal_entry_id", "gl_account_id", "debit", "credit"}),
    EntryCols = Table.SelectColumns(Entries, {"journal_entry_id", "entry_date", "source_type", "memo"}),
    AccountCols = Table.SelectColumns(Accounts, {"gl_account_id", "account_number", "account_name", "account_type"}),

    WithEntry = Table.NestedJoin(LineCols, {"journal_entry_id"}, EntryCols, {"journal_entry_id"}, "Entry", JoinKind.Inner),
    EntryExpanded = Table.ExpandTableColumn(WithEntry, "Entry", {"entry_date", "source_type", "memo"}),
    WithAccount = Table.NestedJoin(EntryExpanded, {"gl_account_id"}, AccountCols, {"gl_account_id"}, "Account", JoinKind.Inner),
    AccountExpanded = Table.ExpandTableColumn(WithAccount, "Account", {"account_number", "account_name", "account_type"}),

    Typed = Table.TransformColumnTypes(AccountExpanded, {
        {"entry_date", type date}, {"debit", type number}, {"credit", type number}
    }),
    WithAmount = Table.AddColumn(Typed, "Amount", each [debit] - [credit], type number),
    Result = Table.RenameColumns(WithAmount, {{"entry_date", "Date"}, {"account_number", "Account"}})
in
    Result
```

Notice that the join keys are the same name in each pair of tables. `JoinKind.Inner` drops lines with no matching entry or account. That is what you want for a report, but it hides orphan lines. Recipe 10 shows how to find them.

**What to check.** Compare the row count with the count in the source table. The sum of `Amount` must be zero for the whole table, and zero for each `journal_entry_id`. The second check is the one that matters for audit: an entry that does not balance is a posting problem in the source system.

## Recipe 10: find unmatched rows with an anti join

**The problem.** Every payment should be applied to at least one invoice, and every application should point to a real payment. Missing matches are where money goes missing. You need the rows on one side that have no partner on the other.

**The M.** `Table.Join` with `JoinKind.LeftAnti` returns the rows from the left table that have no match in the right table. Build the right table first, as a total per key.

```powerquery
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "cpafirm"),
    Payments = Source{[Schema = "dbo", Item = "payments"]}[Data],
    Applications = Source{[Schema = "dbo", Item = "payment_applications"]}[Data],
    Applied = Table.Group(Applications, {"payment_id"}, {
        {"AppliedTotal", each List.Sum([amount_applied]), type number}
    }),
    NotApplied = Table.Join(Payments, {"payment_id"}, Applied, {"payment_id"}, JoinKind.LeftAnti)
in
    NotApplied
```

`NotApplied` lists payments that have no application at all. For payments that are applied but for the wrong amount, use a left outer join and compare the two amounts:

```powerquery
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "cpafirm"),
    Payments = Source{[Schema = "dbo", Item = "payments"]}[Data],
    Applications = Source{[Schema = "dbo", Item = "payment_applications"]}[Data],
    Applied = Table.Group(Applications, {"payment_id"}, {
        {"AppliedTotal", each List.Sum([amount_applied]), type number}
    }),
    Joined = Table.NestedJoin(Payments, {"payment_id"}, Applied, {"payment_id"}, "Applied", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Applied", {"AppliedTotal"}, {"AppliedTotal"}),
    Mismatch = Table.SelectRows(Expanded, each ([AppliedTotal] ?? 0) <> [amount])
in
    Mismatch
```

**What to check.** An anti join that returns rows is a list to investigate, not a conclusion. In the cpafirm sample, every payment has an application that matches its amount, so the mismatch list is empty. That is a result worth recording. The same pattern finds bills with no payment, or bank lines with no ledger entry. Write the count of unmatched rows down each time you refresh.

## Recipe 11: a data quality query that counts problems

**The problem.** Before the report goes out, the controller wants a short list of checks with a count for each. Any count above zero must be explained.

**The M.** Each check is a record with a name and a count. `Table.FromRecords` turns the list of records into a small table. Put it in its own query, and load it to a sheet.

```powerquery
let
    Checks = {
        [Check = "Lines with no amount", Problems = Table.RowCount(Table.SelectRows(Lines, each [Amount] = null))],
        [Check = "Accounts not mapped", Problems = Table.RowCount(Table.SelectRows(Mapped, each [StatementLine] = "Unmapped"))],
        [Check = "Rows with a bad date", Problems = Table.RowCount(Table.SelectRows(Lines, each [Date] = null))],
        [Check = "Lines outside the period", Problems = Table.RowCount(Table.SelectRows(Lines, each [Date] < StartPeriod or [Date] >= Date.AddMonths(StartPeriod, 12)))]
    },
    Result = Table.FromRecords(Checks, type table [Check = text, Problems = Int64.Type])
in
    Result
```

`Lines` and `Mapped` are the earlier queries from recipes 9 and 4. Reference them by query name, and keep them loaded to the data model or as connection-only queries.

Add an entry balance check in the same way. Group the lines by entry, sum the amounts, and count the entries where the total is not zero:

```powerquery
[Check = "Entries that do not balance", Problems = Table.RowCount(Table.SelectRows(
    Table.Group(Lines, {"journal_entry_id"}, {{"Net", each List.Sum([Amount]), type number}}),
    each [Net] <> 0))]
```

**What to check.** Every count should be zero, or have a note next to it that explains why it is not. Refresh the query each time you refresh the report. A check that changes from zero to a number after a refresh is the most useful signal in the workbook.

## Errors and bad rows

Power Query shows an error in a cell as a special value. Two functions handle them:

- `try expression otherwise fallback` gives a fallback value when the expression fails. Use it when a missing value is acceptable, and say so in the notes.
- `Table.RemoveRowsWithErrors(table, {"Amount"})` drops rows whose named column has an error. Use it only after recipe 11 has counted those rows, so the drop is recorded.

Do not use `try` to hide a conversion you have not checked. A date that fails to parse is a file problem, and the fix belongs in the file or in the format string.

## Refresh and audit trail

Keep the steps in Applied Steps in order and name them, so a reviewer can follow them. Keep the source file names in the output (recipe 3 adds a `SourceFile` column), so each figure traces back to a file. Keep the raw export unchanged in its own folder. The query is the audit trail: it is repeatable, and it says exactly what was removed and why.

## Where next

- [M basics](/analyze/power-query-m-basics): queries, values, lists and records.
- [M tables](/analyze/power-query-m-tables): shaping rows, columns, joins and groups in more depth.
- [M functions and patterns](/analyze/power-query-m-patterns): your own functions, error handling and date tables.
- [M performance](/analyze/power-query-m-performance): query folding and when to buffer.
- [DAX for financial reporting](/analyze/dax-financial-reporting): build the statements on top of the cleaned data.
- [SQL for accountants](/analyze/sql-for-accountants): run the same checks in SQL against the source.
- Function pages: [Table.Group](/functions/m/table-group), [Table.Join](/functions/m/table-join), [Table.NestedJoin](/functions/m/table-nestedjoin) and [Table.UnpivotOtherColumns](/functions/m/table-unpivotothercolumns).
