Filter a table by grouping rows: aggregation plus calculations

[pescadicto]

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

[Jimmy801 · Accepted solution]

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

Jimmy

[Jimmy801 · Accepted solution]

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

Jimmy

[pescadicto]

Thank 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?

[Jimmy801 · Accepted solution]

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

[pescadicto]

Great work! thanks @Jimmy801!