digest
Short Summary
Main recommendation: Use Power Query's Group By with Table.AddIndexColumn inside each group to create a running count per product, instead of a calculation-heavy Table.RowCount formula copied down a column. Group the rows by product, add an index column within each group starting at 1, expand the results, then sort back to the original transaction order.
Key Points
- The running count per product is built by grouping rows by the product column, so all rows for the same product (for example, all "quad" rows and all "Carlota" rows) sit together.
- Each group's table gets an index column via Table.AddIndexColumn, which numbers the records 1, 2, 3, and so on within that group.
- The method avoids the slow approach from the previous video, where Table.RowCount inside Table.AddColumn was recalculated for every row as the formula copied down.
- The video credits two viewers, Bill Szysz and Veda, for suggesting the grouping-and-index approach.
Details Worth Noting
- Concrete instructions:
- Select a single cell in the table, then go to Data > Get & Transform > From Table to open it in Power Query.
- Rename the query (for example, "Transaction Product Running Count").
- Right-click the product column and choose Group By. Name the new column (for example, "Table with Index") and keep the default operation, then click OK.
- In the formula bar, expand the previous step's result. Replace the default "each" structure so the calculation runs per row using Table.AddIndexColumn with these arguments: the group table (underscore
_), the new column name in double quotes ("Running Count"), a starting value of 1, and an increment of 1 (giving 1, 2, 3, etc. within each group). Close the nested curly-bracket list properly with the closing parentheses of Table.Group. - Expand the grouped column, uncheck everything except "Running Count" and "Transaction Number," and click OK.
- Drag "Transaction Number" to the left of "Product," then sort it A to Z so rows return to the original order.
- Set data types, for example Whole Number for the numeric columns.
- Faster alternative: after grouping, delete the step that expands the table with index, then in the formula bar use the field access operator on the previous step's column (for example,
[Table with Index]) and wrap it in Table.Combine(...) to merge the tables in one step. Then sort by Transaction Number A to Z. - Choose Close & Load, then load to an existing sheet (the video uses cell J4).
- The video demonstrates both the user-interface method and the faster formula-bar method, and states that the result matches the original order with a correct running count.
- Table names and column headings used in the video (such as "Table with Index") are arbitrary and can be renamed.
Bottom Line
Grouping rows in Power Query and adding an index within each group is a more efficient way to produce a running count per product than row-by-row calculation formulas, and the final sort restores the original transaction order.
