Following up on the Monkey Shorts episode shared yesterday, here's our #PowerQuery Recipe card that outlines the steps to number your data by grouped rows. 👩🍳 You can get the full collection (over 95 handy recipes!) at skw-t.com/pqrecipes
#Excel #PowerBI #DataTransformation #DataPreparation
![Master Your Data in Excel & Power BI
Pattern Difficulty: Lo Code
Number Grouped Rows Recipe
Initial Preparation
Sort any [Dimension] column(s) to group the data
Sort the [Fact] column -> Descending
Re-apply the column data types
Group the Data
Go to Transform -> Group -> Advanced
Group by the [Dimension] column(s)
Add a new column called “Data” -> All Rows
Add Row Numbers
Go to Add Column -> Custom Column
Name: Custom
Formula: =Table.AddIndexColumn([Data], "Index", 1, 1)
Right click the [Custom] column -> Remove Other Columns
Expand all columns from [Custom]
Re-order the columns if desired
(Image of a Source Table containing data first listed by Group, then category. A second Table With Numbered Rows image shows the addition with an index column where each Category within a Group is numbered sequentially by row.)
DBAs know this output as using SQL’s ROW_NUMBER with PARTITION
For more data patterns, check out our courses at skillwave.training](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:ihdq52obivvf6sq3lnalnwrv/bafkreic3owldkbs2x3kxujjl3qafxvataxrkyjyc2lvcrqdbmhc5j7r3x4)
![Master Your Data in Excel & Power BI
Pivoting Stacked Data Recipe
- Add a columns for transaction and line IDs
- Add Column -> Index Column -> From 0
- Select [Index] -> Add Column -> Standard -> Integer-Divide*
- Select [Index] -> Transform -> Standard -> Modulo*
- Pivot the data
Select [Index] -> Transform -> Pivot Column
- Set the Values field to your data column
- Open the Advanced option and “Don’t Aggregate”
- Right click the [Integer-Division] column -> Remove
- Rename the newly created columns
- Set your data types
*Modulo and Integer Division always target the number representing the 1st row of the 2nd transaction
Recipe 10.120.2112
For more data patterns, check out our courses at skillwave.training](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:ihdq52obivvf6sq3lnalnwrv/bafkreif5b4ytc5womlmee233vx7t4vdrpoads3e7arelwaxd6bsvrk5j6e)
