← Previously: Power Query from Scratch, Part 2: Importing and combining without the copy-paste (9 Jul) → Next: Power Query from Scratch, Part 4: Custom columns and your first look at M (23 Jul)
What this post covers: unpivoting a wide report into a usable long table, splitting a combined column into its real parts without losing data on inconsistent rows, and grouping by a key while keeping the detail rows the reporting UI usually throws away.
The report that was built for a printer, not a query
Somewhere in your building there's a monthly regional sales report that looks perfect in an email. Region and Rep down the left, twelve month columns across the top, a wall of numbers in between. It was designed to be looked at once, not queried, and every time finance asks what March looked like for the Auckland team, someone opens it, scrolls right, and does the arithmetic by eye.
That's the file this post uses. One "Region - Rep" column holding two pieces of information mashed into one, and twelve month columns that should be a single Month column and a single Sales column. Fixing both problems is three operations: unpivot, split column, group by. Individually they're a few clicks. The part nobody explains is where each one breaks the next time the file changes shape, which is the only reason this needs three thousand words instead of three screenshots.
The unpivot that survives a column nobody's added yet
Select the twelve month columns, right-click a header, choose Unpivot Columns, and Power Query collapses them into two: Attribute and Value, which you rename to Month and Sales. Twelve columns become two, and the row count multiplies by twelve. That part's mechanical.
The step Power Query writes is where the actual decision lives:
= Table.Unpivot(Source, {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, "Month", "Sales")Table.Unpivot takes an explicit list of the columns to fold down. That list is frozen the moment the step is created. Add a "Jan-27" column to next year's export and this step ignores it, no error, no warning, the new column just sits there unfolded next to Month and Sales looking exactly as wrong as it is.
Right-click the same selection and choose Unpivot Other Columns instead, and you get this:
= Table.UnpivotOtherColumns(Source, {"Region - Rep"}, "Month", "Sales")This version lists the columns to leave alone, not the columns to fold. Every column that isn't "Region - Rep" gets unpivoted, including one that doesn't exist yet. A refresh next year against a file with a new month column keeps working without you touching this step. The UI defaults to whichever option matches how many columns you selected relative to the total, so check which one you actually got before moving on. This is the single most common reason a working Power Query refresh quietly stops matching the source data: someone used Unpivot Columns on a report that grows a column every month, and nobody noticed until a total went missing.
The split that survives a name nobody's typed yet
"Region - Rep" needs to become Region and Rep. Select the column, Split Column, By Delimiter, type a hyphen, done, in about four seconds. The step:
= Table.SplitColumn(Source, "Region - Rep", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Region", "Rep"})This works perfectly on "Auckland - Priya" and "Wellington - Marcus". It works less perfectly the day a rep is entered as "Wellington - Mary-Anne Smith", because now there are two hyphens in the string and the split has to guess which one is the real delimiter. Power Query's delimiter split has a "split at" option, left-most or right-most, buried under Advanced options, and whichever one you picked determines whether Mary-Anne loses her hyphen or her region does. Neither failure throws an error. The row just has the wrong value in the wrong column, sitting there looking plausible until someone filters by region and the numbers don't add up.
When the source data isn't guaranteed to have exactly one delimiter per row, By Delimiter is the wrong tool even though it's the first one the UI offers. Split by Number of Characters, taking a fixed number of characters from the right, is more durable when the format is consistent in length even if the content isn't, since "Auckland - " is always eleven characters however the rep's name that follows varies. For genuinely inconsistent source data, the honest fix is upstream: get whoever exports this report to hand over two columns in the first place. Power Query can absorb a lot of bad habits from source systems. It shouldn't be asked to absorb an ambiguous delimiter.
The Group By that doesn't throw away the columns you'll want next week
Now the shape is right: Region, Rep, Month, Sales, one row per rep per month. Finance wants regional totals. Select Region, Transform ribbon, Group By, choose Sum of Sales, and Power Query hands back exactly two columns: Region and the sum. Rep, Month, every other column in the table, gone. Not hidden, not collapsed, gone from this step onward.
That's the basic Group By, and it's correct when a two-column summary really is all you need. It's the wrong default the moment someone downstream asks which rep drove that regional number, because the rows that would answer it no longer exist in the query.
The advanced Group By dialog has a second way to define an aggregated column, and this is the one worth knowing exists before you need it:
= Table.Group(Source, {"Region"}, {
{"Detail", each _, type table},
{"TotalSales", each List.Sum([Sales]), type number}
})each _ in an aggregation slot doesn't calculate anything. It packages every row belonging to that group into a nested table and puts it in a cell. The Detail column looks like an empty "Table" link until you click it, at which point every rep and month for that region is still there, one click away. You get the regional total finance asked for, and the detail nobody asked for yet but will, in about a week, when someone wants to know why Wellington dipped in June.
Frequently Asked Questions
What's the difference between Unpivot Columns and Unpivot Other Columns?
Unpivot Columns hard-codes the exact list of columns to fold and ignores anything added later. Unpivot Other Columns hard-codes the columns to leave alone instead, so any new column gets folded in automatically on the next refresh. For a report that grows a column on a schedule, like a new month every month, Unpivot Other Columns is almost always the one you want.
Why did my split column put the wrong text in the wrong output column?
The delimiter you split on appeared more than once in at least one row, and the split had to choose whether to match the left-most or right-most occurrence. Check the Advanced options under Split Column By Delimiter for a "split at" setting, or switch to Split by Number of Characters if the format has a consistent length even when the content varies.
Can I get my other columns back after a Group By deletes them?
Not from that step directly, since Table.Group only outputs the columns you defined. Go back to the Group By step, reopen it, switch to the advanced option, and add an aggregation using each _ to capture the full row set per group as a nested table before you commit to summarising it away.
Does Unpivot Other Columns break if the source has zero extra columns some months?
No. It unpivots whatever is present at refresh time, so a month with no bonus column just produces fewer output rows for that period rather than an error. It only causes a problem in the other direction, when a column you expected to survive as an identifier, like Region, accidentally gets swept into the unpivot because it wasn't in the leave-alone list.
Is Group By's advanced mode slower than the basic version?
Marginally, because nesting full row tables per group holds more data in memory than a single aggregated number. On a report with thousands of rows rather than millions, the difference isn't something you'll notice. If performance genuinely matters at scale, aggregate what you need with basic Group By and keep a separate un-grouped query for detail lookups instead of nesting everything by default.
Can I split a column into more than two parts at once?
Yes, Split Column By Delimiter splits on every occurrence of the delimiter by default, producing as many output columns as there are delimiter-separated segments. The single-delimiter ambiguity problem above gets worse with more segments, since there are more places for an unexpected extra delimiter to shift everything one column to the right.
Where the series goes from here
The next two parts move from combining and reshaping into writing logic by hand. Power Query from Scratch, Part 4: Custom columns and your first look at M is where the UI runs out, in the specific and inevitable case where no button does the one thing you need, and you write your first custom column formula instead. Power Query from Scratch, Part 5: Parameters, functions and a model that refreshes itself turns everything from the series into a reusable function, so next month's file needs one click instead of a rebuild.
Part 4 lands 23 Jul.