Power Query for accounting data
Fix the import problems finance teams meet: bank exports, trial balances, monthly folders, account mapping and data quality checks, with M you can paste.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Clean bank and credit card exports into one consistent table.
- Turn a trial balance or a monthly budget grid into a table a model can use.
- Combine monthly files from a folder and map accounts with a fallback.
- Run data quality checks that count the problems before a report goes out.
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 first. The 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).
"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:
#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:
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
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.
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.
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.
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:
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.
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:
[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 fallbackgives 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: queries, values, lists and records.
- M tables: shaping rows, columns, joins and groups in more depth.
- M functions and patterns: your own functions, error handling and date tables.
- M performance: query folding and when to buffer.
- DAX for financial reporting: build the statements on top of the cleaned data.
- SQL for accountants: run the same checks in SQL against the source.
- Function pages: Table.Group, Table.Join, Table.NestedJoin and Table.UnpivotOtherColumns.
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.