Power Query in Excel: clean a ledger export once, refresh it every month

Power Query is the tool in Excel for importing and cleaning data with steps that are recorded and can be run again. You clean a messy export once; next month you drop in the new file, click Refresh, and every step repeats. This guide cleans a sales ledger export with title rows, text amounts, CR suffixes and day-first dates, and checks the result against the export's own total.

What Power Query is

Power Query sits on the Data tab under Get Data, in the group labeled Get & Transform Data. It connects to a source (a workbook, a CSV file, a folder, a database) and opens the Power Query Editor, where each change you make is saved as a named step in the Applied Steps list. Microsoft's overview describes it the same way: the steps are recorded and run again on every refresh.

Underneath, every step is a line of M, the Power Query formula language; Home > Advanced Editor shows the whole query. You rarely need to type M, but reading it tells you what a step does.

Power Query is built into Excel 2016 and later for Windows and into Microsoft 365. On a Mac it needs Microsoft 365 and Excel for Mac 16.69 or later, under Data > Get Data (Power Query), and the Mac list of sources is shorter than the Windows one.

The raw export

The file is a March 2026 sales ledger export in USD from an ERP set to day-first dates, as a subsidiary or shared-service center outside the US often runs it. It opens like this:

Every value arrives as text. Six problems: a title row, blank rows, a trailing space in one ID, amounts with thousands separators, a credit written as 1,200.00CR instead of a negative, and dates in day/month/year order. On a machine set to US dates, 04/03/2026 means April 3 unless you say otherwise.

Step by step: from raw to clean

Connect. Data > Get Data > From File > From Excel Workbook (or From Text/CSV for a .csv export), choose the file, select the sheet, and click Transform Data, not Load. If Power Query added its own Promoted Headers and Changed Type steps, delete both from Applied Steps (the X beside each) so you start from the raw text. The step names in the snippets below are shortened; Power Query's own are longer, such as #"Removed Top Rows".

Remove the title and blank row. Home > Remove Rows > Remove Top Rows, enter 2:

= Table.Skip(Source, 2)

Promote the headers. Home > Use First Row as Headers:

= Table.PromoteHeaders(Skipped, [PromoteAllScalars=true])

Drop the blank rows and the footer. Click the filter arrow on Date and clear (blank) and Total, or edit the step to:

= Table.SelectRows(Promoted, each [Date] <> null and [Date] <> "" and [Date] <> "Total")

Five rows remain.

Trim the IDs. Select Customer ID, then Transform > Format > Trim. C-1001 becomes C-1001, which matters for the merge below:

= Table.TransformColumns(Filtered, {{"Customer ID", Text.Trim, type text}})

Turn the amounts into signed numbers. Add Column > Custom Column, name it Net amount, and enter:

if Text.EndsWith([Amount], "CR")
then -Number.From(Text.Replace(Text.Replace([Amount], "CR", ""), ",", ""), "en-US")
else Number.From(Text.Replace([Amount], ",", ""), "en-US")

Set the new column's type to Decimal Number, then remove the original Amount column. The culture argument of Number.From sets which characters are the decimal point and thousands separator; en-US reads a period as the decimal point and a comma as the thousands separator, which is what this export uses. A European export written as 12.400,00 needs de-DE.

Read the dates with a locale. Right-click the Date header > Change Type > Using Locale, choose Data Type Date and Locale English (United Kingdom):

= Table.TransformColumnTypes(Amounts, {{"Date", type date}}, "en-GB")

Now 04/03/2026 is read as March 4, and Excel displays it as 03/04/2026 on a US machine. Microsoft's data types page walks through the same case. An export already in US month-first order uses "en-US" instead.

The clean rows:

Name the query Ledger in the Query Settings pane.

Merge with the customer master

The customer master is an Excel Table named Customers, loaded with Data > From Table/Range:

C-1004, Northgate Retail, has not been added yet. In the Ledger query choose Home > Merge Queries, pick Customers as the second table, click Customer ID in both, set Join Kind to Left Outer (all from first, matching from second), and click OK:

= Table.NestedJoin(Typed, {"Customer ID"}, Customers, {"Customer ID"}, "Customers", JoinKind.LeftOuter)

Click the expand icon on the new Customers column, tick Customer and Segment, and clear "Use original column name as prefix". All five rows stay; the C-1004 row keeps its $3,150 with null in Customer and Segment. That is the point of a left outer join: unmatched rows stay visible. Filter Segment for null to list them. Without the Trim step, the $12,400 row would have been a null too.

Group By for a summary

Right-click Ledger in the Queries pane and choose Reference, then name the new query By customer. Choose Transform > Group By: group by Customer ID, new column name Net, operation Sum, column Net amount:

= Table.Group(Source, {"Customer ID"}, {{"Net", each List.Sum([Net amount]), type number}})

C-1001 is $12,400 less the $1,200 credit. Home > Close & Load To lets you load each query as a table on a sheet, or as a connection only with "Add this data to the Data Model" ticked, where Power Pivot can relate it to other tables. A pivot table built on the loaded Ledger gives the same totals by any other field.

The check: clean total equals the report footer

The export states its own total, 28,610.00. The clean rows must sum to the same figure: $12,400 + $3,150 + $8,760 − $1,200 + $5,500 = $28,610. Keep that comparison as a query so it runs on every refresh.

In Ledger, right-click the Filtered step and choose Extract Previous; name the new query Raw. It holds the rows up to the promoted headers, footer included. Then Data > Get Data > From Other Sources > Blank Query, open the Advanced Editor, and enter:

let
    FooterText = Table.SelectRows(Raw, each [Date] = "Total"){0}[Amount],
    Footer = Number.From(Text.Replace(FooterText, ",", ""), "en-US"),
    Clean = List.Sum(Ledger[Net amount]),
    Result = #table({"Footer", "Clean total", "Difference"}, {{Footer, Clean, Clean - Footer}})
in
    Result

Name it Check and load it to a sheet. Difference must be 0. Had the merge been an inner join, it would read -3,150. That footer is a control total: a figure from the source, compared with what you built.

Refresh next month

Two ways to point the query at the new file:

• A fixed file name. Save each month's export over the same file, for example sales-ledger.xlsx in the same folder, and choose Data > Refresh All.
• A folder. Data > Get Data > From File > From Folder, then Combine & Transform. Power Query applies the steps you define on a sample file to every file in the folder, so a year of monthly exports becomes one table. Folder is a Windows source; it is not in the Mac list.

To move the source, choose Data > Get Data > Data Source Settings > Change Source. After each refresh read Check first. The routine around it is in the monthly refresh in twenty minutes, and what a data validation report should tell you lists what else to confirm.

Where it goes wrong

Changing the date type without a locale. On a US-set machine, 04/03/2026 becomes April 3 and 11/03/2026 becomes November 3, silently; 18/03/2026 and later give errors. The errors are the lucky rows.

Filtering on a value that changes. If next month's footer reads "Grand Total", a filter on "Total" lets it through and the footer is summed into the data. Filter on what every real row has instead: each [Customer ID] <> null and Text.Trim([Customer ID]) <> "".

Removing duplicates on the whole table. Two genuinely identical invoice lines, same date, customer and amount, become one. Remove duplicates only on a key such as the invoice number.

An inner join in Merge Queries. Ledger rows with no master record disappear, $3,150 in this example. Use Left Outer and count the nulls.

Renamed columns. Steps refer to columns by name. If the export renames Amount to Net Amount, refresh fails with an error that the column 'Amount' of the table wasn't found. Rename it back in an early step, or edit the steps that use it.

Skip the cleaning steps

The steps above are the ones described tool-free in how to prepare a sales export for analysis. Covirage does them on upload, shows what it changed, and checks the cleaned total against the export's own footer; deterministic tools do the parsing and the sums, and the external AI model only explains the result. Upload the raw export and the title rows, CR suffixes and totals are handled and checked for you: see Covirage for finance teams. For why a checked file is a sound basis for analysis, read why files, not connectors.

title: "Power Query in Excel: clean a ledger export once, refresh it every month" description: "A step-by-step guide to Power Query in Excel using a messy sales ledger export: removing title and blank rows, trimming IDs, turning CR suffixes into negative amounts, reading day-first dates with the right locale, merging a customer master and grouping by customer. It ends with the check that the cleaned total equals the export's own footer, and the monthly refresh." seoTitle: "Power Query in Excel: clean a ledger export" metaDescription: "Power Query is Excel's tool for importing and cleaning data with recorded, repeatable steps. Clean a ledger export and check it against its total." date: 2026-09-30 category: guides industry: finance-teams solution: excel-analysis keywords: ["power query", "power query excel", "microsoft power query", "excel query", "what is power query", "power query merge", "get and transform data excel"] answer: "Power Query is the data import and cleaning tool built into Excel (Data > Get Data, also called Get & Transform). You connect to a file, apply steps such as removing title rows, trimming text, fixing types and merging tables, and Excel records them. Next month you replace the file and click Refresh, and the same steps run again. It is in Excel 2016 and later." faq:

• q: "Is Power Query free in Excel?" a: "Yes. It is built into Excel 2016 and later for Windows, and into Microsoft 365. For Excel 2010 and 2013 it was a free add-in. On a Mac it needs a Microsoft 365 subscription and Excel for Mac version 16.69 or later, under Data > Get Data (Power Query)."
• q: "What is the difference between Power Query and Power Pivot?" a: "Power Query imports and cleans data. Power Pivot models it: relationships between tables and DAX measures in the Data Model. A common workflow uses Power Query to clean each file and Power Pivot to relate and measure them."
• q: "Is Power Query better than VLOOKUP?" a: "For joining whole tables that are refreshed regularly, often yes: Merge Queries joins once per refresh and keeps unmatched rows visible with a left outer join. For a one-time lookup in a small sheet, a formula is quicker."
• q: "What language does Power Query use?" a: "Power Query records each step in M, the Power Query formula language. You can see and edit it in the Advanced Editor. Most users never write M directly, but reading it helps when a step breaks."

Power Query is the tool in Excel for importing and cleaning data with steps that are recorded and can be run again. You clean a messy export once; next month you drop in the new file, click Refresh, and every step repeats. This guide cleans a sales ledger export with title rows, text amounts, CR suffixes and day-first dates, and checks the result against the export's own total.

What Power Query is

Power Query sits on the Data tab under Get Data, in the group labeled Get & Transform Data. It connects to a source (a workbook, a CSV file, a folder, a database) and opens the Power Query Editor, where each change you make is saved as a named step in the Applied Steps list. Microsoft's overview describes it the same way: the steps are recorded and run again on every refresh.

Underneath, every step is a line of M, the Power Query formula language; Home > Advanced Editor shows the whole query. You rarely need to type M, but reading it tells you what a step does.

Power Query is built into Excel 2016 and later for Windows and into Microsoft 365. On a Mac it needs Microsoft 365 and Excel for Mac 16.69 or later, under Data > Get Data (Power Query), and the Mac list of sources is shorter than the Windows one.

The raw export

The file is a March 2026 sales ledger export in USD from an ERP set to day-first dates, as a subsidiary or shared-service center outside the US often runs it. It opens like this:

Every value arrives as text. Six problems: a title row, blank rows, a trailing space in one ID, amounts with thousands separators, a credit written as 1,200.00CR instead of a negative, and dates in day/month/year order. On a machine set to US dates, 04/03/2026 means April 3 unless you say otherwise.

Step by step: from raw to clean

Connect. Data > Get Data > From File > From Excel Workbook (or From Text/CSV for a .csv export), choose the file, select the sheet, and click Transform Data, not Load. If Power Query added its own Promoted Headers and Changed Type steps, delete both from Applied Steps (the X beside each) so you start from the raw text. The step names in the snippets below are shortened; Power Query's own are longer, such as #"Removed Top Rows".

Remove the title and blank row. Home > Remove Rows > Remove Top Rows, enter 2:

= Table.Skip(Source, 2)

Promote the headers. Home > Use First Row as Headers:

= Table.PromoteHeaders(Skipped, [PromoteAllScalars=true])

Drop the blank rows and the footer. Click the filter arrow on Date and clear (blank) and Total, or edit the step to:

= Table.SelectRows(Promoted, each [Date] <> null and [Date] <> "" and [Date] <> "Total")

Five rows remain.

Trim the IDs. Select Customer ID, then Transform > Format > Trim. C-1001 becomes C-1001, which matters for the merge below:

= Table.TransformColumns(Filtered, {{"Customer ID", Text.Trim, type text}})

Turn the amounts into signed numbers. Add Column > Custom Column, name it Net amount, and enter:

if Text.EndsWith([Amount], "CR")
then -Number.From(Text.Replace(Text.Replace([Amount], "CR", ""), ",", ""), "en-US")
else Number.From(Text.Replace([Amount], ",", ""), "en-US")

Set the new column's type to Decimal Number, then remove the original Amount column. The culture argument of Number.From sets which characters are the decimal point and thousands separator; en-US reads a period as the decimal point and a comma as the thousands separator, which is what this export uses. A European export written as 12.400,00 needs de-DE.

Read the dates with a locale. Right-click the Date header > Change Type > Using Locale, choose Data Type Date and Locale English (United Kingdom):

= Table.TransformColumnTypes(Amounts, {{"Date", type date}}, "en-GB")

Now 04/03/2026 is read as March 4, and Excel displays it as 03/04/2026 on a US machine. Microsoft's data types page walks through the same case. An export already in US month-first order uses "en-US" instead.

The clean rows:

Name the query Ledger in the Query Settings pane.

Merge with the customer master

The customer master is an Excel Table named Customers, loaded with Data > From Table/Range:

C-1004, Northgate Retail, has not been added yet. In the Ledger query choose Home > Merge Queries, pick Customers as the second table, click Customer ID in both, set Join Kind to Left Outer (all from first, matching from second), and click OK:

= Table.NestedJoin(Typed, {"Customer ID"}, Customers, {"Customer ID"}, "Customers", JoinKind.LeftOuter)

Click the expand icon on the new Customers column, tick Customer and Segment, and clear "Use original column name as prefix". All five rows stay; the C-1004 row keeps its $3,150 with null in Customer and Segment. That is the point of a left outer join: unmatched rows stay visible. Filter Segment for null to list them. Without the Trim step, the $12,400 row would have been a null too.

Group By for a summary

Right-click Ledger in the Queries pane and choose Reference, then name the new query By customer. Choose Transform > Group By: group by Customer ID, new column name Net, operation Sum, column Net amount:

= Table.Group(Source, {"Customer ID"}, {{"Net", each List.Sum([Net amount]), type number}})

C-1001 is $12,400 less the $1,200 credit. Home > Close & Load To lets you load each query as a table on a sheet, or as a connection only with "Add this data to the Data Model" ticked, where Power Pivot can relate it to other tables. A pivot table built on the loaded Ledger gives the same totals by any other field.

The check: clean total equals the report footer

The export states its own total, 28,610.00. The clean rows must sum to the same figure: $12,400 + $3,150 + $8,760 − $1,200 + $5,500 = $28,610. Keep that comparison as a query so it runs on every refresh.

In Ledger, right-click the Filtered step and choose Extract Previous; name the new query Raw. It holds the rows up to the promoted headers, footer included. Then Data > Get Data > From Other Sources > Blank Query, open the Advanced Editor, and enter:

let
    FooterText = Table.SelectRows(Raw, each [Date] = "Total"){0}[Amount],
    Footer = Number.From(Text.Replace(FooterText, ",", ""), "en-US"),
    Clean = List.Sum(Ledger[Net amount]),
    Result = #table({"Footer", "Clean total", "Difference"}, {{Footer, Clean, Clean - Footer}})
in
    Result

Name it Check and load it to a sheet. Difference must be 0. Had the merge been an inner join, it would read -3,150. That footer is a control total: a figure from the source, compared with what you built.

Refresh next month

Two ways to point the query at the new file:

• A fixed file name. Save each month's export over the same file, for example sales-ledger.xlsx in the same folder, and choose Data > Refresh All.
• A folder. Data > Get Data > From File > From Folder, then Combine & Transform. Power Query applies the steps you define on a sample file to every file in the folder, so a year of monthly exports becomes one table. Folder is a Windows source; it is not in the Mac list.

To move the source, choose Data > Get Data > Data Source Settings > Change Source. After each refresh read Check first. The routine around it is in the monthly refresh in twenty minutes, and what a data validation report should tell you lists what else to confirm.

Where it goes wrong

Changing the date type without a locale. On a US-set machine, 04/03/2026 becomes April 3 and 11/03/2026 becomes November 3, silently; 18/03/2026 and later give errors. The errors are the lucky rows.

Filtering on a value that changes. If next month's footer reads "Grand Total", a filter on "Total" lets it through and the footer is summed into the data. Filter on what every real row has instead: each [Customer ID] <> null and Text.Trim([Customer ID]) <> "".

Removing duplicates on the whole table. Two genuinely identical invoice lines, same date, customer and amount, become one. Remove duplicates only on a key such as the invoice number.

An inner join in Merge Queries. Ledger rows with no master record disappear, $3,150 in this example. Use Left Outer and count the nulls.

Renamed columns. Steps refer to columns by name. If the export renames Amount to Net Amount, refresh fails with an error that the column 'Amount' of the table wasn't found. Rename it back in an early step, or edit the steps that use it.

Skip the cleaning steps

The steps above are the ones described tool-free in how to prepare a sales export for analysis. Covirage does them on upload, shows what it changed, and checks the cleaned total against the export's own footer; deterministic tools do the parsing and the sums, and the external AI model only explains the result. Upload the raw export and the title rows, CR suffixes and totals are handled and checked for you: see Covirage for finance teams. For why a checked file is a sound basis for analysis, read why files, not connectors. For the formula alternative to a merge, see XLOOKUP vs VLOOKUP, and for the same cleaning and joins in a database, see SQL for data analysis.