Power Query from Scratch, Part 5: Parameters, functions and a model that refreshes itself
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.
Data Transformation
// 29 postsTutorials, tips, and deep dives on Data Transformation — every post from bianca.codes tagged Data Transformation.
Data Transformation posts
-
-
Build a star schema from a single flat file
One wide CSV, thousands of repeated names. By the end you have a fact table, two dimension tables, and a model Power BI actually wants.
-
Power Query from Scratch, Part 4: Custom columns and your first look at M
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.
-
Power Query from Scratch, Part 3: The transforms that actually matter
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.
-
Power Query from Scratch, Part 2: Importing and combining without the copy-paste
Files, folders, and multiple sheets go in three different ways, and the append-versus-merge decision is where most people guess wrong.
-
When did the spreadsheet quietly become a database - and why that's a problem
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.
-
Power Query from Scratch, Part 1: The mental model nobody teaches you
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 Hardest Part of Working With Messy Data Isn't the Cleaning - It's Knowing When to Stop
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.
-
If You've Copied the Same Power Query Steps Across Three Queries, You've Made a Mistake
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.
-
Build a Reusable Power Query Function That Cleans Any Text Column to Title Case
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.
-
Power Query Custom Functions: Write Once, Call From Any Query in Your Workbook
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.
-
3-Minute Win: Create a Power Query Parameter to Switch Between Dev and Prod Data Sources
A query parameter holds your data source path, so switching from a test CSV to the production SharePoint file is one dropdown, not a find-and-replace through your M code.
-
3-Minute Win: Use REDUCE to Concatenate a Column of Values Into One Cell
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.
-
Star Schema Done Right, Part 1: What a Fact Table Actually Is (And Isn't)
Most introductions to star schema start with the diagram. The star, the centre, the spokes. This one starts with the question: what problem does a fact table solve that a flat table doesn't? Part 1 builds the intuition before the vocabulary, so the rest of the series makes immediate sense.
-
MAP: Apply Any Transformation to Every Element in an Array - Without a Loop
MAP takes your LAMBDA and runs it on every element in an array. No helper columns, no dragging formulas, no VBA loops.
-
What I Wish Someone Had Told Me About Working With Data in a Small Team
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.
-
3-Minute Win: Use Power Query's 'Keep Rows Where' Filter Without Writing a Line of M
The Keep Rows filter in the Power Query UI handles 80% of row filtering tasks without you ever opening the Advanced Editor. Most people skip past it and go straight to writing M. Five clicks and you're done, and the M it generates is clean enough to modify later.
-
The Skill Nobody Told You Was Half the Job: Asking Good Questions
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.
-
Python for Excel Users, Part 3: Merging Datasets and Replacing Your VLOOKUPs
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.
-
How to Say No to a Data Request Without Burning the Relationship
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…
-
Python for Excel Users, Part 2: Grouping, Aggregating, and Replacing Your SUMIFS
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…
-
Getting Credit for Work That 'Looks Easy'
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.
-
Python for Excel Users, Part 1: Reading and Filtering Your First Real Dataset
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…
-
Build a Word template that holds its formatting
Your Word templates shouldn't fight you on formatting.
-
Table.Group in Power Query: The Aggregation That Hands You Back a Table
Table.Group in Power Query returns a table of tables, unlocking complex multi-level analysis you can't get anywhere else.
-
The Flow Ran Perfectly for Two Months. Then It Didn't.
You built the flow. It ran perfectly for two months. Then a colleague renamed a SharePoint column, or moved a file, or changed a dropdown value - and everything stopped. This isn't bad luck. It's…
-
Nobody Knows What You Actually Do (And That's Partly Your Fault)
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…
-
Build a Drop-In XLOOKUP Upgrade for Every VLOOKUP in Your Workbook
Stop replacing VLOOKUPs. Your workbook deserves XLOOKUP without touching the formula.
-
The state of email merge in 2026
It is 2026, and we are still stringing together premium Power Automate connectors just to avoid the Word mail merge wizard.