A Love Letter to Power Query
This one goes out to all you software developers working so-called “programming-adjacent jobs” (it’s just data analysis; you’re not fooling anyone)
Power-What?
If you’ve spent an iota of time working in Excel, you’ve probably had a boss or manager ask you to do something that’s clearly stretching the capabilities of the software.
If you’re unlucky enough, you’ve probably heard this exact phrase muttererd (not for the faint of heart!):
Can you make it populate the cell, but not if I’ve typed something in it already?
Now, before you start looking for a new job (or becoming a macro wizard), you might have come across a neat little tool called Power Query.
What is Power Query Actually?
I didn’t feel like looking up or actually caring what it’s supposed to do, so I’m just going to let Copilot take over from here:
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. Power Query is also available as a free add-in for Excel and Power BI Desktop.
So Power Query lets you connect your Excel sheet to a bunch of other data sources. It’s a godsend for web scraping and pulling from large datasets (I’m looking at you, SharePoint folder with 1000s of CSV files!)
OK That’s Cool, But Where Is This Going?
I haven’t even gotten to the interesting part. That’s right, I made you read through all this fluff you probably don’t even care about. I’m not sorry.
Introducing Power Query M
Here’s what it looks like:
Source = Table.FromColumns({{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}, {"A", "B", "C"}),
#"Summed Rows" = Table.AddColumn(Source, "Sum", each List.Sum({[A], [B], [C]}))—and it’s not just any programming language… It’s a functional programming language! That’s right; Microsoft has blessed our plebian lives with an entire purely functional programming language in Excel. It’s like Haskell, but for Excel! (I’m kidding, it’s not like Haskell at all)
It’s crazy to think that we got to the point where Microsoft Excel has four separate, Turing-complete languages, yet if I zoom out too fast on an empty sheet, it crashes. But I digress.
The language is designed to do set-based operations, just like in SQL, but also more Excel-y things like “pivot-ing” and “unpivot-ing” rows/columns (Microsoft-speak for “transposing”). Once you’re familiar with it, it’s quite the powerful tool.
The Real Kicker
When your coworker opens the Power Query editor, and clicks “Merge Queries”, all it’s doing is appending #"New Step" = Table.NestedJoin(#"Last Step", ...) to the M code. Every single button you can click in the GUI is just a wrapper around the M code!
Of course, this means that every time you open a shared spreadsheet, you can justfully proclaim how bad your coworkers’ queries are, spend 4 hours refactoring them, then proceed to be the only person ever to touch that file ever again. All in a day’s work, am I right?
Wrapping Up
In the example above, about the formula that populates a cell only if it’s empty, you can do that with a single line of M code:
= Table.AddColumn(Source, "New Column", each if [Old Column] = null then "New Value" else [Old Column])Furthermore, you can use self-referencing tables; a strategy of creating two copies of a table (one from the source data, and one from the sheet itself), then merging the two on refresh. This allows you to update the contents of the table, while keeping any desired manual changes.
If you can’t put two and two together, know that this could have fixed every single time your boss tried to put a formula next to a PivotTable, just to have it immediately become misaligned or overwritten. A dream come true for any software-dev-turned-boss’s-Excel-monkey.
If you do regular work in Excel, I highly recommend spending some time to check out Power Query and its M language. It’s a great way to automate repetitive or fragile tasks, and it’s an awesome segue into Power BI, which will increase your value as an employee.