Forum Discussion
Solved
Filtering 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...
Here you go. Place this code in a blank query to see the steps. The last one is the relevant one:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7NCsMwDIPfJediHMvpz3Flb1F6C8lod/P7wxJ3G/RihD4sadvCY43MYQjFpnmxJiITwj5cJDbjQIKkJhLFP5AO3udLoE2Jkt5QLvN0Zf18cI+wktsdaXR3fYp311JNAK/gGzkPiPYGpeUL4KMsV39A37R/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Feature1 = _t, Feature2 = _t]), #"Replaced Value" = Table.ReplaceValue(Source,".",",",Replacer.ReplaceText,{"Feature2"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"ID", type text}, {"Feature1", type text}, {"Feature2", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Feature1", each List.First([Feature1])}, {"Feature2", each List.Sum([Feature2])}}) in #"Grouped Rows"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi pescadicto, try this code.
It returns the first option.
Its basically a "group by", then the column2 disappers, but later it is returned back (for each ID, the first occurance of "feature1" is returned)
let Query3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7NCsMwDIPfJediHMvpz3Flb1F6C8lod/P7wxJ3G/RihD4sadvCY43MYQjFpnmxJiITwj5cJDbjQIKkJhLFP5AO3udLoE2Jkt5QLvN0Zf18cI+wktsdaXR3fYp311JNAK/gGzkPiPYGpeUL4KMsV39A37R/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column3", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Value", each List.Sum([Column3]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN(Table.SelectRows(#"Changed Type",(inner)=>inner[Column1]=[Column1]),1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2"}, {"Column2"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Column1", "Column2", "Value"}) in #"Reordered Columns"
Thanks AlB ! This is the table:
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7NCsMwDIPfJediHMvpz3Flb1F6C8lod/P7wxJ3G/RihD4sadvCY43MYQjFpnmxJiITwj5cJDbjQIKkJhLFP5AO3udLoE2Jkt5QLvN0Zf18cI+wktsdaXR3fYp311JNAK/gGzkPiPYGpeUL4KMsV39A37R/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t])
ID Feature1 Feature2
AB100 fs789s 10.3
AB101 j35325 5.1
AB102 jlkh234 24.4
AB102 df87 10.4
AB305 sfd 6.6
BD200 gfgs233 5.0
BD200 kj3244 4.9
BD301 sdg33 3.1
Hi pescadicto, try this code.
It returns the first option.
Its basically a "group by", then the column2 disappers, but later it is returned back (for each ID, the first occurance of "feature1" is returned)
let
Query3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7NCsMwDIPfJediHMvpz3Flb1F6C8lod/P7wxJ3G/RihD4sadvCY43MYQjFpnmxJiITwj5cJDbjQIKkJhLFP5AO3udLoE2Jkt5QLvN0Zf18cI+wktsdaXR3fYp311JNAK/gGzkPiPYGpeUL4KMsV39A37R/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column3", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Value", each List.Sum([Column3]), type nullable number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN(Table.SelectRows(#"Changed Type",(inner)=>inner[Column1]=[Column1]),1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2"}, {"Column2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Column1", "Column2", "Value"})
in
#"Reordered Columns"
thanks Bohumil_Uhrin !