Tutorials, tips, and deep dives on Power Query & Transformation — every post from bianca.codes tagged Power Query & Transformation.
Two skills, the same effort to learn, wildly different payoff. Automation deletes its own evidence, which is why 'just tell them' never works, and what to do instead.
A hardcoded folder path and a copy-pasted business rule are the same problem, and parameters plus functions are how you stop editing M code every month.
Three parts of buttons, then a request that needs an 'and' the Conditional Column dialog doesn't have - time to type your first line of M.
One clean Excel table and a Word template are all it takes for forty personalised reports, once the wizard is driven properly.
Unpivot, split column and group by all look foolproof, right up until the source file adds a column or a name gets typed slightly differently than expected.
Files, folders, and multiple sheets go in three different ways, and the append-versus-merge decision is where most people guess wrong.
Nobody decided to build a database out of that 80-tab workbook - it ended up doing a database's job without any of a database's guarantees.
Most people learn Power Query by clicking through wizards. Nobody explains what it's actually doing - and that gap is why queries break in confusing ways.
The mechanics of cleaning data are the straightforward part. The hard part has no function and no checklist: deciding which imperfections actually matter, and stopping there.
Copying a query and tweaking the copy feels efficient. It isn't - three copies of the same logic is three places to update and three ways for it to drift.
The same trim-and-title-case steps are copy-pasted across three of your queries - write them once as a function instead, and fix the rule in one place.
You've built the same cleanup steps in four different queries. A custom function is how you write them once and call them from all four.
Joining a column of names or tags into one comma-separated string used to need TEXTJOIN or a helper column. REDUCE does it in one formula, and lets you transform each item before it gets concatenated.
You'll build a dropdown list that reads from a structured table and includes every new row automatically. No named range maintenance, no 'why isn't my new entry showing up' moments.
MAP takes your LAMBDA and runs it on every element in an array. No helper columns, no dragging formulas, no VBA loops.
In a small team, you're the analyst, the engineer, the dashboard designer, and the person who explains what a p-value is at the all-hands.
The analysts who get traction aren't the fastest at pulling data, they're the best at unpacking what was actually being asked. The skill is asking good questions, and nobody trains for it.
VLOOKUP and XLOOKUP, but for files that don't fit in memory, joins on multiple keys, and many-to-many matches that VLOOKUP simply can't do.
You can't build every dashboard that gets requested. But 'no' lands differently depending on how you say it — and most data people either say yes to everything and resent it, or say no in ways that…
SUMIFS and COUNTIFS are great — until your data has 500,000 rows and Excel is grinding. Part 2 shows how to do everything your SUMIFS does, but with pandas groupby — faster, more flexible, and on d…
The more competent you get, the less people see the effort behind what you deliver. A clean dashboard looks effortless. A fast turnaround looks like it was always fast.
This isn't 'what is a variable' Python. This is: you have a CSV that's too big for Excel, you need it filtered by date and category, and you need the result saved as a clean file. Part 1 covers exa…
Most dashboards answer the question 'what happened?' Very few answer 'what should we do?' The gap isn't data — it's intent. A dashboard built for the former often actively gets in the way of the la…
They ask for a pie chart when they need a bar chart. They ask for a table when they need a trend line. This isn't ignorance — it's that they're translating a question into a visual type without kno…
You delivered a clean model, a solid dashboard, a real solution — and got a blank look in the room. The work was right. But explaining technical work to someone who doesn't share your frame of refe…
You'll build a reusable Power Query that finds the most recent entry per group - the kind of query that normally tempts people into messy MAXIFS workarounds in Excel. We're doing it properly, wit…
Stop cringing. There's a way to talk about your accomplishments in Excel and Power BI that sounds competent, not arrogant. It's all about framing.
The advice is everywhere: if you're serious about data, you need SQL. But for most analysts already working in Excel and Power BI, the gap isn't SQL knowledge - it's data modelling instinct. Here…
You deliver good work consistently, your manager knows you're reliable — and yet somehow you're invisible at promotion time. The problem isn't your output. It's that you've never made your work l…
Stop replacing VLOOKUPs. Your workbook deserves XLOOKUP without touching the formula.
It is 2026, and we are still stringing together premium Power Automate connectors just to avoid the Word mail merge wizard.