I’ve used Power Query to solve a lot of finance reporting problems. Writing up five of them, I noticed that the same few habits kept doing most of the work.
The five cases
- Turning a wide allocation sheet into a journal upload
- Building a journal from sheets that can’t be tables
- Replacing 11 query branches with one rules table
- Rebuilding a database P&L crosstab in Power Query
- Consolidating the same sheet from many team workbooks
The lessons
1. Fix types at the door. Text dates and amounts stored as text caused more breakages than logic errors did. Set types once, at the start. (Cases 3 and 4)
2. Put business rules in tables, not code. A mapping sheet beats a pile of if branches, and finance users can maintain it without touching M. (Case 3)
3. Reconcile at the lowest useful level. A grand total tells you there’s a gap. Account-level totals tell you why. (Case 1)
4. Decide upfront where rounding goes. Name the line that absorbs the cents, and add a guard so a real error can’t pass as “rounding”. (Cases 1 and 2)
5. Shrink before you join or pivot. Trim columns, filter rows and group first. Buffer small lookup tables. (Case 4)
6. Design for messy inputs. Use MissingField.UseNull, filter out lock files, and keep a source-file column so every line can be traced. (Cases 2 and 5)
7. Use named ranges when tables aren’t an option. INDEX-based dynamic names give Power Query a clean source that grows with the data. (Case 2)
A note on these write-ups
All the names, file paths and figures have been changed, and every number is a made-up example. The M code is a clean reconstruction of each approach, not the production code, so test it on your own data first.