Forum Discussion
Dynamic Table.Group with dynamic amount of Columns
Hello,
First, I want to excuse my bad English, nevertheless i hope you can understand my problem.
I want to group with sum agregation several columns of a table - with the Table.Group function. My problem is I dont have a fix list of columns. The number of columns is variable (can be 1,2,3,.... columns).
My start/source table is called table1.
My List which includes all columns I want to group is called List1.
I cant find a solution for my problem, most times it is an error.
Table.Group(table1, {"RepeatGroup"},....
I dont know what the last part of the function is.
I hope someone can help me. My further questions please ask.
=Table.Group(Table1,"RepeatGroup",List.Transform(List1,each {_,(x)=>List.Sum(Table.Column(x,_))}))
2 Replies
Hi rhenus-florian - thank you for your question. I could follow what you are asking.
I would like to suggest using the Unpivot Other Columns feature in Power BI before Grouping. The approach will lead to Group By including "RepeatGroup, Attribute, Value". Table.UnpivotOtherColumns - PowerQuery M | Microsoft Learn. The Attribute column will dynamically change for the new columns.
After using Group By, you can alway Pivot back to have the columns, but this may not be necessary.
=Table.Group(Table1,"RepeatGroup",List.Transform(List1,each {_,(x)=>List.Sum(Table.Column(x,_))}))
