Power BI Power Query:
Data Transformation for Finance
Clean ERP Exports in Minutes, Not Days
Every GCC finance team I work with has the same problem: their ERP export is a mess. P&L data is pivoted with months as columns. Account codes and descriptions are in one merged cell. Budget is in a separate file with different column names. Three subsidiaries export in slightly different formats. Before you write a single DAX measure, you need Power Query to transform this chaos into a clean, consistent star schema. This post teaches you exactly how.
What you will master in this post
- Navigate the Power Query interface and understand the Applied Steps pane — your transformation audit trail
- Unpivot wide P&L tables (months as columns) into the long format that DAX requires
- Fix data types, replace errors, handle null values from SAP/Oracle exports automatically
- Merge the Actuals query with the Budget query — building a unified fact table in one step
- Append 12 monthly files into a single table without manual copy-paste
- Write custom M code columns for account hierarchy, fiscal year flags, and entity codes
- Use Parameters for dynamic file paths — so the dashboard works on any machine
Why Power Query is the Most Important Skill in the Finance BI Stack
Finance teams spend 80% of their Power BI time on data preparation and 20% on dashboard building. It should be the other way around. Power Query — the ETL layer inside Power BI — automates the entire data cleaning and shaping process. Once built, transformations run automatically every time you click Refresh. The SAP export that used to take 3 hours to manually clean and reshape in Excel now takes 8 seconds.
Power Query uses the M language — a functional programming language that is fully auditable (every transformation is a step you can see, edit, or delete), repeatable (the same steps run on every refresh), and document-able (other team members can read and maintain your logic).
Power Query Interface — The Three Panels You Must Know
Queries pane (left)
Lists all your queries — Actuals, Budget, dDate, dAccount, dEntity. Organise them into folders: Fact Tables, Dimension Tables, Staging (intermediate queries not loaded to the model). Staging queries reduce duplication — define a transformation once and reference it from multiple downstream queries.
Applied Steps pane (right)
Every action you take in Power Query creates a named step — Source, Promoted Headers, Changed Type, Removed Columns, Unpivoted Other Columns, etc. Each step is a line of M code. Click any step to see the data at that point. Delete a step to undo it. Rename steps to document what they do.
Formula bar (top)
Shows the M code for the selected step. You can edit it directly — this is where you graduate from point-and-click to writing M. Most finance transformations are accomplished with 10–15 standard functions. You do not need to memorise the entire M language.
Cleaning a Typical SAP/Oracle Finance Export
A typical SAP GL or Oracle Financials export for GCC companies arrives as a wide table — account codes in rows, months (Jan, Feb, Mar…) as columns. This format is great for humans to read but impossible for DAX to work with. The fix is Unpivot.
Step 1: Unpivoting P&L Data
COGS | (4,100) | (4,200) | (4,500)
SG&A | (1,200) | (1,250) | (1,300)
⚠ DAX cannot aggregate across month columns
Revenue | Feb-25 | 8,450
COGS | Jan-25 | (4,100)
✓ One row per account per month — DAX-ready
// Full code is gated.
// Enrol in Power BI Mastery to access
// the complete copy-paste-ready implementation.// Measure logic hidden — enrol in Power BI Mastery to get
// the complete, copy-paste-ready code for this calculation.
// Preview: this measure uses CALCULATE with filter context
// to return the correct value for each visual in your dashboard.
Leave a Comment
Share your Power Query experience. Register or log in to comment.
By commenting you agree to our community guidelines.