# Tags **Software** : [[02 - Tags/Software/Power Query]] **Functions** used : [[Table.Transpose]] [[Table.AddIndexColumn]] [[Table.Group]] [[Table.PromoteHeaders]] [[Table.TransformColumnTypes]] [[Number.From]] [[List.Sum]] [[List.Range]] [[Table.SelectColumns]] [[Table.RenameColumns]] [[Table.ExpandTableColumn]] [[Table.RemoveColumns]] [[Excel.CurrentWorkbook]] **Date** : 16-March-2025 # Description - In this rather esoteric situation i show you how to use the 5th argument (comparer) of Table.Group to dynamically add a Grouping index column based on a sum of rows. # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/ahCTKWQvfJI?si=PbnhOZ23JNmf1QIP" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe> [GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_Grouping_Video_2.xlsx) # M-Query ```Query 1 - Parameter let Source = Excel.CurrentWorkbook(){[Name="Param"]}[Content], Custom1 = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"GroupingLimit", Int64.Type}}) in #"Changed Type" ``` ```Query 2 - Main Result let Source = Excel.CurrentWorkbook(){[Name="BrokerTrades"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Date", "Index"}, {{"Detail", each _}},0, (Curr,Next)=> Number.From(List.Sum(List.Range(#"Added Index"[BrokerFee], Curr[Index], Next[Index] - Curr[Index] + 1))>Param[GroupingLimit]{0}) ), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Detail"}), #"Added Index1" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1, Int64.Type), #"Renamed Columns" = Table.RenameColumns(#"Added Index1",{{"Index", "Fee Bucker Group"}}), #"Expanded Detail" = Table.ExpandTableColumn(#"Renamed Columns", "Detail", {"Date", "Number Stocks", "Price", "Amount", "BrokerFee", "Index"}, {"Date", "Number Stocks", "Price", "Amount", "BrokerFee", "Index"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Detail",{{"Date", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Number Stocks", type number}, {"Price", type number}, {"Amount", type number}, {"BrokerFee", type number}}) in #"Changed Type1" ``` ## Optional Tags **Tags** : **Technique** : [[Input Parameter]] [[Dynamic Grouping]] [[Inner Outer Tables]]