Forum Discussion
Filter a table by grouping rows: aggregation plus calculations
Hi everybody!
I am a powerbi's newbie, after read in these forums a lot of related-messages I can't solve this: I'm trying to filter a table by grouping rows according to a criterion (rows with same ID) while making a calculation on the values of the rows grouped (sum of Feature2) and record it by overwriting the same column (or adding that value in a new column). I apologize for my english.
In a nutshell:
What I have is:
What I want is (1):
or (2):
I have been looking for two kinds of solutions without success:
I) A filter in the Power Query Editor
I tried to apply a Grouped Rows step in the Power Query Editor:
= Table.Group(#"Filtered Rows", {"ID"}, {{"NewFeature", each List.Sum([Feature2]), type nullable number}})
but this removes the rest of the columns which I want to keep.
I performed some workarounds with Table.AddColumn, with no succesfull.
II) A new table obtained through a DAX expression
Table = GROUPBY(OriginalTable,OriginalTable[ID],"New Column",SUMX(CURRENTGROUP(),OriginalTable[Feature2]))
but it doesn't work: the rest of the columns and the applied filters are lost.
Any suggestions? Thanks in advance
Hello pescadicto
you have to add one more function to the group function, that takes the grouped table. Afterwards you can expand it again. Here a example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMAxKGSrE60UpOQFZSmiGQNELiGwFJYzDfGchKBik3AXNdgKwUsHJTOD8ZrNxMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Feature1 = _t, Feature2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Feature1", type text}, {"Feature2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"NewFeature", each List.Sum([Feature2]), type number}, {"AllRows", each _, type table [ID=text, Feature1=text, Feature2=number]}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Feature1", "Feature2"}, {"Feature1", "Feature2"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllRows",{"ID", "Feature1", "Feature2", "NewFeature"}) in #"Reordered Columns"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello pescadicto
check out this dynamic solution. Note, that in the expand-function I had to delete the "ID", otherwise this column would be double and the program throw and error
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMAxKGSrE60UpOQFZSmiGQNELiGwFJYzDfGchKBik3AXNdgKwUsHJTOD8ZrNxMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Feature1 = _t, Feature2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Feature1", type text}, {"Feature2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"NewFeature", each List.Sum([Feature2]), type number}, {"AllRows", each _, type table [ID=text, Feature1=text, Feature2=number]}}), GetColumnNamesOfTableToBeExpanded = List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[AllRows], each Table.ColumnNames(_)))), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows",List.Difference(GetColumnNamesOfTableToBeExpanded, {"ID"}), List.Difference(GetColumnNamesOfTableToBeExpanded, {"ID"})), #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllRows",{"ID", "Feature1", "Feature2", "NewFeature"}) in #"Reordered Columns"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
4 Replies
Hello pescadicto
you have to add one more function to the group function, that takes the grouped table. Afterwards you can expand it again. Here a example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMAxKGSrE60UpOQFZSmiGQNELiGwFJYzDfGchKBik3AXNdgKwUsHJTOD8ZrNxMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Feature1 = _t, Feature2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Feature1", type text}, {"Feature2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"NewFeature", each List.Sum([Feature2]), type number}, {"AllRows", each _, type table [ID=text, Feature1=text, Feature2=number]}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Feature1", "Feature2"}, {"Feature1", "Feature2"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllRows",{"ID", "Feature1", "Feature2", "NewFeature"}) in #"Reordered Columns"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyThank you! It worked. My real table has a lot of columns. It is posible to reformulate
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Feature1", "Feature2"}, {"Feature1", "Feature2"}),in such a way that the column names do not have to be made explicit?
Hello pescadicto
check out this dynamic solution. Note, that in the expand-function I had to delete the "ID", otherwise this column would be double and the program throw and error
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMAxKGSrE60UpOQFZSmiGQNELiGwFJYzDfGchKBik3AXNdgKwUsHJTOD8ZrNxMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Feature1 = _t, Feature2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Feature1", type text}, {"Feature2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"NewFeature", each List.Sum([Feature2]), type number}, {"AllRows", each _, type table [ID=text, Feature1=text, Feature2=number]}}), GetColumnNamesOfTableToBeExpanded = List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[AllRows], each Table.ColumnNames(_)))), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows",List.Difference(GetColumnNamesOfTableToBeExpanded, {"ID"}), List.Difference(GetColumnNamesOfTableToBeExpanded, {"ID"})), #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllRows",{"ID", "Feature1", "Feature2", "NewFeature"}) in #"Reordered Columns"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
