Forum Discussion
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 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.
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"
6 Replies
Hi pescadicto
Can you please show your sample data (the first table in particular) in text-tabular format in addition to (or instead of) the screen captures? So that it can be copied easily and we can run a quick test. Just copy the table as text paste it here.
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
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.1Here 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
