Power Query for Accounting: Automating Data Cleaning & Transformation in Excel

What Is Power Query — ETL Inside Excel

Power Query is a data transformation and connection technology built into Microsoft Excel (available since Excel 2016 on the Data ribbon as "Get & Transform Data"). It implements the classic ETL pattern — Extract, Transform, Load — directly within your familiar spreadsheet environment.

Extract: Connect to data sources — CSV files, Excel workbooks, SQL databases, SharePoint lists, web pages, or cloud services — and pull data into the Power Query engine.

Transform: Clean, reshape, filter, merge, and calculate the data using a visual, point-and-click interface (or the underlying M language for advanced work).

Load: Output the clean, transformed data to an Excel table, to the data model (Power Pivot), or to Power BI.

Power Query vs Traditional Excel Formulas

This comparison is critical for understanding why Power Query is a game-changer for accounting workflows:

Aspect	Traditional Excel Formulas	Power Query
Repeatability	Manual — redo every month	Automated — click Refresh
Data volume	Slows above 100K rows	Handles millions of rows efficiently
Combining files	Copy-paste manually	Folder connector combines all files automatically
Audit trail	Hidden in cell formulas	Visible step-by-step in Applied Steps pane
Error handling	Propagates silently	Flags errors visibly, allows replacement
Learning curve	Low (already known)	Medium (2–3 days to become productive)
Maintenance	Update formulas each period	Update source data, refresh query

The key insight: Power Query is repeatable, formulas are manual. For month-end close tasks that run every single month, Power Query pays back its learning investment within weeks.

The Power Query Editor Walkthrough

Launch Power Query via Data → Get Data → From File (or From Other Sources). Once your data is loaded, the Power Query Editor opens. Understanding its layout is essential.

The Four Key Areas

1. Queries Pane (left panel): Lists all queries in your workbook. You can create multiple queries — one for bank data, one for GST data, one for the merged output — and they all appear here. Queries can depend on each other (referenced queries).

2. Formula Bar (top, with M language): Every transformation step generates an M formula automatically. For example, when you click "Remove Duplicates," the formula bar shows:
= Table.Distinct(#"Previous Step")
You can edit these formulas directly for advanced customisation, but clicking buttons generates them automatically for most tasks.

3. Applied Steps Pane (right panel — Query Settings): This is the audit trail of your transformation. Every action you take adds a step here. You can click any step to see the data at that point in the process, delete a step to undo it, or reorder steps by dragging. This is the most powerful feature for understanding and troubleshooting transformations.

4. Data Preview (centre): Shows a sample of the transformed data at the currently selected Applied Step. Column headers show data types (ABC for text, 123 for whole number, 1.2 for decimal, calendar icon for date).

Data Type Assignment — A Critical Step

Always verify data types when data first loads. A common accounting error: Excel imports invoice amounts as text (because the source CSV had currency symbols like ₹ or commas). Power Query will flag this — click the column header type icon and change to Decimal Number. Dates from Indian bank statements often import as text in DD/MM/YYYY format; change the type to Date using locale "English (India)" to parse correctly.

Key Transformations for Accountants

These are the most frequently used Power Query transformations in accounting workflows, with practical accounting context for each.

Remove Duplicates

Home tab → Remove Rows → Remove Duplicates. In accounting: detect duplicate invoices in AP ledger, deduplicate GSTR-2A entries where the same invoice appears twice due to amendments. Always apply after defining which columns constitute a "unique" record — select only the relevant columns (Invoice No + Vendor GSTIN + Amount) before removing duplicates, not all columns.

Split Column — Bank Statement Date+Narration

Indian bank statements from SBI, HDFC, ICICI often combine the transaction date with the narration in a single column like "12/03/2024 UPI/123456789/AMAZON". Use Transform → Split Column → By Delimiter (use "/" as delimiter, or split at first space) to separate the date portion from the narration. Then convert the date portion to a proper Date type.

Trim & Clean

Transform → Format → Trim removes leading and trailing spaces. Transform → Format → Clean removes non-printable characters (common in SAP and Tally exports). These two operations solve the most common reason why VLOOKUP returns errors — the account names look identical but have invisible character differences. Always Trim and Clean text columns before any merge or lookup operation.

Replace Values — Standardising Account Names

Transform → Replace Values. Use case: your trial balance has "Sundry Debtors", "Trade Receivables", and "Accounts Receivable" all referring to the same account due to different team members exporting differently from Tally. Replace all variants with the canonical name from your chart of accounts. For bulk replacements, consider a reference table approach using Merge Queries instead.

Merge Queries — The VLOOKUP Replacement

Home → Merge Queries. This is Power Query's join operation — equivalent to VLOOKUP/INDEX-MATCH but more powerful. Merge types:

Merge Type	SQL Equivalent	Accounting Use Case
Inner Join	INNER JOIN	Matched invoices — invoices present in both AP ledger and bank statement
Left Outer	LEFT JOIN	All AP invoices + matching bank entries (unmatched AP items show null)
Right Outer	RIGHT JOIN	All bank entries + matching AP items (unmatched bank items show null)
Full Outer	FULL OUTER JOIN	All records from both — complete reconciliation view
Left Anti	NOT IN subquery	Invoices in AP ledger with NO matching bank payment

Key accounting workflow: Merge GST data (from GSTR-2A) with your GL data (from Tally/SAP) on the supplier GSTIN column. After expanding the merged table, you can compare the GST amount claimed in your books against what appears in GSTR-2A — the core of GST input credit reconciliation.

Append Queries — Combining 12 Monthly Files Into One Annual Dataset

Home → Append Queries. Stack multiple tables vertically (like SQL UNION ALL). The classic accounting use case: you have 12 monthly bank statement CSV files in a folder. Instead of manually copy-pasting, use Data → Get Data → From Folder, select the folder, and Power Query automatically appends all CSV files into one table. It adds a "Source.Name" column showing which file each row came from (useful for identifying the month). When you receive January next year's file, just drop it in the folder and refresh — it automatically includes it.

Group By — Pivot-Style Summarisation

Transform → Group By. Equivalent to a Pivot Table, but the output is a clean table (not a pivot format) and is part of the query chain. Example: Group the journal entries table by Account Code and Month, sum the Debit and Credit columns to produce a trial balance summary. This replaces SUMIF arrays that break on large datasets.

Custom Columns with M Formula

Add Column → Custom Column. Write M expressions for calculations not available in the UI. Common accounting examples:

// Classify payment terms
= if [DueDate] - [InvoiceDate] <= 30 then "Net 30"
  else if [DueDate] - [InvoiceDate] <= 60 then "Net 60"
  else "Net 90+"

// Calculate days overdue (AR aging)
= Duration.Days(DateTime.LocalNow() - [DueDate])

// Extract financial year from date (Indian FY)
= if Date.Month([TransactionDate]) >= 4
  then "FY" & Text.From(Date.Year([TransactionDate]))
  else "FY" & Text.From(Date.Year([TransactionDate]) - 1)

Real Accounting Use Cases

Use Case 1: Combining Multiple Bank Statement CSVs

Scenario: Your company has current accounts with SBI, HDFC, and ICICI. The treasury team downloads monthly CSVs from each bank's internet banking portal. Currently, a junior accountant spends 2 hours every month manually combining and cleaning these files.

Power Query solution: Create three queries (one per bank), applying bank-specific cleaning (SBI has different column names than HDFC), then append all three into a "Master Bank Statement" query. Total setup time: 3 hours. Ongoing monthly time: 2 minutes to refresh.

Use Case 2: Cleaning GSTR-2A Downloads

The GSTR-2A Excel download from the GST portal is notoriously messy: merged cells in headers, blank rows separating supplier sections, dates in inconsistent formats (some DD-Mon-YYYY, some DD/MM/YYYY), and supplier names with trailing spaces. Power Query steps:

1. Remove top N rows (headers from the portal)
2. Use the first row as headers
3. Filter to remove blank rows (filter where Invoice Number is not null)
4. Trim and Clean all text columns
5. Replace date format: Transform → Replace Values to standardise separators, then convert to Date type using "English (India)" locale
6. Remove columns that are always empty in the download

Use Case 3: Mapping Trial Balance to Reporting Template

Your Tally trial balance exports account codes that don't match the structure of your company's management reporting template (which groups accounts into P&L and Balance Sheet categories). Maintain a mapping table in Excel: two columns — Tally Account Code and Reporting Category. Merge this mapping table with the trial balance data on Account Code (Left Outer Join). The result: each trial balance row gets its Reporting Category. Then Group By Reporting Category to produce the management report. When the chart of accounts changes, update the mapping table only — not the entire report template.

Use Case 4: Automating Vendor Reconciliation

Match vendor invoices (from AP ledger) to bank payments (from bank statement) to identify outstanding liabilities. Steps:

1. Query 1: AP Ledger — filter for the vendor, extract Invoice No, Amount, Date
2. Query 2: Bank Statement — filter for the vendor's bank account, extract Reference, Amount, Date
3. Merge Query: Full Outer Join on Amount (and approximate date — within 5 days — using a conditional approach)
4. Add a custom column: if AP row is null → "Bank only", if Bank row is null → "Unmatched Invoice", else → "Matched"
5. Filter to show unmatched items only — these are your reconciling items

Advanced Topics, M Language & Performance

Refresh vs Re-Run

This is Power Query's core value proposition. When source data updates — new bank statement arrives, GSTR-2A is updated — you do not redo the transformation. You click Data → Refresh All (or right-click the query → Refresh). Power Query re-runs all the Applied Steps automatically against the new data. This is fundamentally different from Excel formulas, which recalculate but require manual updates to the underlying data structure.

Power Query as the Entry Point to Power BI

The Power Query interface in Excel is identical to the Power Query interface in Power BI Desktop. Every query you build in Excel can be copied directly to Power BI. The M language code is fully compatible. This means mastering Power Query in Excel is simultaneously preparing you for Power BI — the next step in the analytics career path. Finance professionals who have built Excel Power Query workflows find the transition to Power BI significantly easier.

M Language Basics for Advanced Users

M (Mashup language) is a functional, case-sensitive language. Every query is ultimately an M expression. Key concepts:

// Every query is a "let" expression
let
    // Step 1: Connect to source
    Source = Excel.Workbook(File.Contents("C:\Data\TB.xlsx"), null, true),

    // Step 2: Select sheet
    Sheet1 = Source{[Item="TrialBalance",Kind="Sheet"]}[Data],

    // Step 3: Promote headers
    PromotedHeaders = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),

    // Step 4: Change types
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders,
        {{"Account Code", type text}, {"Debit", type number}, {"Credit", type number}}),

    // Step 5: Filter out zero rows
    FilteredRows = Table.SelectRows(ChangedTypes,
        each [Debit] <> 0 or [Credit] <> 0)
in
    FilteredRows  // This is the output

Performance Tips for Large Accounting Datasets (>100K Rows)

Tip	Why It Helps
Filter rows early in the query	Reduces data volume for all subsequent steps
Remove unnecessary columns early	Less data to process through each step
Load to Data Model, not worksheet	Data Model (Power Pivot) handles larger volumes
Avoid expanding all columns in Merge	Only expand the specific columns you need
Use typed columns consistently	Prevents type conversion overhead in later steps
Disable background refresh for large queries	Prevents Excel freezing during work
Use Table.Buffer() for repeated reference tables	Caches small lookup tables in memory