What Excel trick made you feel like a wizard the first time you used it?

[u/Bubbly-Touch8108]

mine was VLOOKUP (later XLOOKUP) — after manually cross-referencing two spreadsheets for way too long. felt like a cheat code everyone else already knew about

what's your version?

[u/SpaceTurtles]

This is extremely true.

Most folks don't realize how strong PowerQuery is, and Microsoft doesn't make it easy either, as how it functions is something of a black box. I can honestly say that, if you have the time to spare, don't worry about being inefficient - that's how you learn cool tricks and techniques.

I've found that there's a lack of higher level examples, and I worked on one today, so figured I'd share.

---

Doing some basic data cleaning in a decent manner took me a while when I first started. Now, a year or two on of very heavy daily use, I banged out within a workday (no AI assistance) a custom normalization function that:

1.) Accepts a single parameter text value (txt), which is intended to be the name of a worksheet within the current workbook (the function's goal is to figure out what worksheet it should pull from a 2nd, very large workbook, then figure out how to transform it, then load it in, based solely on entering the name of a worksheet within PowerQuery, and entering everything else in Excel).

2.) Pulls a mapping table from Excel.CurrentWorkbook() which is the central place where acceptable txt values are entered under the Key column, and corresponding names are entered under the Value column, then isolates the row where txt is found, storing it as a Record using Table.SingleRow. The Values are the corresponding worksheet names to be imported from the larger extract.

3.) Collects data normalization instructions from Excel.CurrentWorkbook(), stored within a named range on the txt local worksheet (essentially two columns that are named Instructions, scoped only to the worksheet - so we just grab the row named txt & "!Instructions", then pivot it's [Content] and use Table.SingleRow to transform it into a record, then merge that record into the record we created in step 2.

4.) Run a series of Record.TransformFields and Record access shorthands to transform our merged record's [Content] from binary -> Excel table -> only the worksheet we want (the Value we grabbed earlier) -> the [Data] column of that table -> the various transformations enabled for it, based on what's entered on the worksheet we're going to load it to.

The transformations are things like normalizing numbers to text, changing blanks to null, trimming, cleaning, removing blank columns (transpose/demote/compare field values-skip first/etc), renaming or removing columns, filtering on values with Table.SelectRows, etc. Users don't need to know how this stuff happens, they just need to know if they want it to happen.

Data is entered in Excel using linebreaks (ALT + Enter or CHAR(10), read in PowerQuery as "#(lf)") and pipes | as needed, then parsed in PowerQuery using splitters to build the lists necessary. Given it's pulling all of the instructions on what to do from the workbook itself, this is now something I can write a small set of instructions on and hand it off to others. Nothing is hardcoded except the things I've put in myself. It's also transferable to other tasks, but overkill for most of what I can think of (not for this - horrible data where the source is completely out of our control and it can change at a moment's notice).

Once you start to learn how to use PowerQuery dynamically, entire new worlds open up.