digestVidSign in
summary

excelisfun - Power Query Running Count Based on Row Condition – Excel Magic Trick 1588

Thumbnail of the video “excelisfun - Power Query Running Count Based on Row Condition – Excel Magic Trick 1588”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology
Tags: science, technology
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: Use Power Query, not a worksheet COUNTIFS formula, when your data is destined for Power Pivot, Power BI, or a large or complicated import, and build the running count with a custom column that filters a buffered copy of the table. The video explains that Power Query has no cell references, so each row must filter the full table with Table.SelectRows to create an expandable range.

Key Points

  • Running counts require an order: the column must be sorted (the example uses a transaction number, though a date or time could also work) for the count to make sense.
  • In Power Query, a row cannot look at a column range the way Excel cell references do; each row must rebuild the full table and filter it, which the presenter describes as an unfamiliar concept for Excel users.
  • Table.Buffer loads the table once so every row reuses it instead of reloading it.
  • The condition is a two-part comparison: the transaction number must be less than or equal to the current row's value, and the product must equal the current row's product (the "and" operator is used to combine them).
  • The final count uses Table.RowCount to count the filtered rows, and the result is set to a whole-number data type.

Details Worth Noting

  • Concrete instructions:
    • If the data is already in an Excel table, go to Data, Get and Transform from Table to open Power Query, and name the query (e.g., "Product Running Count").
    • Sort the transaction column using the drop-down and choose Sort Descending.
    • Add a step with the fx button using Table.Buffer on the previous step's table, then rename it "Buffer Table" (press F2 in Applied Steps).
    • Go to Add Column, Custom Column, name the column, enter any placeholder value such as 1, and click OK.
    • Edit the formula to use Table.AddColumn with the buffer table, explicitly define functions as (variable) => instead of "each," and use Table.SelectRows on the buffer table with a condition referencing the inside table's Transaction Number column via an Expand step.
    • Add the product condition with "and" (lowercase) comparing the inside table's Product column to the outside row's Product.
    • Wrap the filtered result in Table.RowCount, check the expandable running count values, then add a whole-number data type.
    • Choose Home, Close and Load, and load to the existing sheet.
  • The presenter notes the method is harder than COUNTIFS and recommends it only when Power Query is needed.

Bottom Line

A running count in Power Query is achievable but requires rebuilding filtered tables per row, so it is best reserved for situations where data is already flowing through Power Query rather than a simple worksheet task.