Filtering 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.

[AlB · Accepted solution]

@pescadicto 

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 

[AlB]

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 

[pescadicto]

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

[Bohumil_Uhrin · Accepted solution]

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"

[pescadicto]

thanks @Bohumil_Uhrin !

[AlB · Accepted solution]

@pescadicto 

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 

[pescadicto]

thank you @AlB !