Power Query updates my reporting with one click
Here's what my reporting used to look like.
An ugly profit and loss. Rows of accounts and nothing else.
So I built three dashboards on top of it. A KPI view, a comparison against prior period and prior year, and a summary view that lets me flip between months, quarters, and years.
They looked great.
And then next month rolled around…
New export, new accounts that weren't there in July, and me relinking every dashboard while my coffee went cold next to me.
One to two hours. Every single month.
I'll admit… I did that for way longer than I should have.
Then I learned Power Query.
Now I point Excel at the new month's data, hit one button, and all three dashboards update for August.
Alright, so here's why linking dashboards is such a pain in the first place.
It has nothing to do with your formulas.
It's the shape of your data.
A normal profit and loss runs two directions at once. Accounts down the side, dates across the top, values sitting in the middle.
Easy to read.
Miserable to work with.
Power Query fixes that, and the tool itself is way simpler than people think. You're just teaching Excel the cleanup steps once, so every month you hit refresh and it runs them again.
So I take that wide, messy layout and pivot it.
One clean table, top to bottom. A single row for every account and every month.
And once your data looks like that, everything after it gets easy.
Now you need to tell those dashboards which line rolls into which number.
Nobody wants to scroll through forty line items.
They want a handful of numbers that actually matter. So subscription revenue, product revenue, and services revenue all roll up into one line called revenue.
The obvious move is to add a mapping column right onto your profit and loss.
Don't.
Because next month a new account shows up, and you're back digging through your source data trying to remember where it belongs.
So I keep the mapping in its own little table instead, and let Power Query connect the two with a merge.
A merge is really just an XLOOKUP. Match on the account name, pull the grouping across.
But it never touches your source data.
Change something in that mapping table, hit refresh, and it flows through everywhere.
That's the piece that lets you drop in a fresh export every month and have it just work.
With the data clean and mapped, the dashboards are almost boring to build. A SUMIFS on the summary grouping and the date range, copied across.
But here's the part I really want you to steal.
At the bottom of every dashboard, I build an error check. It compares the dashboard back to the source, and it screams the second the two don't match.
So when I load August and something doesn't tie, I'm not annoyed.
I'm relieved.
Because that flag is Power Query telling me a new account showed up.
And one more query lists exactly which ones. Last month it was implementation income and bonuses.
I map those two, hit refresh, and every dashboard updates at once.
That whole afternoon I used to lose? Now it's one button.
Now, I'll be honest with you.
Setting this up the first time is real work. The queries, the mapping table, the error checks.
But you build it once… and every month after is a single click. Coffee still hot.
That's a big part of why I built Model Wiz, where all of this comes already wired in.
Either way, the setup above is yours to keep.
But enough about my close.
What's the one thing in yours that still eats hours it shouldn't? Is it the relinking? The mapping?
Hit reply and let me know. I read every single one.
Josh (Your CFO Guy)
Thanks for reading my article. My goal is to help you grow in your career with proper Finance and Accounting.
If we're not already connected on Social media, let's get connected and send me a message to say hi. I promise to respond
