Power BI Power Query: Data Transformation for Finance
Clean SAP and Oracle exports, reshape P&L data, merge budgets with actuals, and automate recurring finance transformations.
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.
- 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
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →Step 2: Fixing Data Types from ERP Exports
The most common data quality failure in SAP/Oracle exports: Amount columns exported as Text because of thousands separators (commas) or currency symbols (SAR, $). Power Query treats "8,200" as text, not a number — so your SUM() returns blank. Fix: Replace Values (remove comma and currency symbol) → then Change Type to Decimal Number. Build this into your query so it runs automatically on every refresh.
Full code is gated
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →Step 3: Handling Nulls and Errors
Full code is gated
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →Merging Actuals with Budget — Building a Unified Fact Table
The cleanest approach for a finance dashboard: keep Actuals and Budget as separate queries, then merge them in a third query to create a single unified fact table. This avoids DAX complexity from maintaining two separate fact tables with different relationship paths.
Full code is gated
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →Get the complete Power Query finance pipeline — Power BI Mastery exclusive
The full .pbix file with pre-built Power Query transformations for SAP/Oracle exports, budget merge, date table generation, and parameter-driven file paths — exclusively for Power BI Mastery students.
Enrol in Power BI Mastery →Appending Multiple Monthly Files Automatically
Many GCC companies receive monthly finance exports as separate files (Jan_2025.xlsx, Feb_2025.xlsx, etc.). Power Query's Folder connector lets you point to a folder and automatically combine all files — no manual copy-paste, and new files added to the folder appear automatically on the next refresh.
Full code is gated
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →Custom Columns — Adding Business Logic in M
Full code is gated
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →Parameters — Dynamic File Paths
The file path problem: If you hard-code "C:\Khalid\Finance\SAP_Export.xlsx" in your query and share the file with a colleague, it breaks immediately on their machine. The fix: use a Power Query Parameter for the base folder path. One parameter, referenced in all queries. To change the source location, update the parameter once — all queries update automatically.
Full code is gated
Copy-paste-ready Power Query M code is available inside Power BI Mastery.
Unlock Full Code — Power BI Mastery →
Dr. Hesham Mokhiemer
Global Distinguished Trainer · IFRS Expert · Founder, The Financeer AcademyDr. Hesham Mokhiemer has trained over 18,000 finance professionals across Saudi Arabia, UAE, Kuwait, Bahrain, and Egypt in IFRS, Power BI, and financial reporting. He is the founder of The Financeer Academy and the creator of the DipIFR Masterclass and CertIFR preparation courses.
