Power Query: aggregation plus calculations

Question

I'm working in a PowerBi report, trying to pre process data with Power Query. 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:

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

What I want is (1):

ID      Feature1    Feature2                                        
AB100   fs789s      10.3    
AB101   j35325      5.1 
AB102   jlkh234     34.8    
AB305   sfd         6.6 
BD200   gfgs233     9.9 
BD301   sdg33       3.1

or (2):

ID      Feature1    Feature2    NewFeature                                  
AB100   fs789s      10.3        10.3        
AB101   j35325      5.1         5.1     
AB102   jlkh234     24.4        34.8        
AB102   df87        10.4        34.8        
AB305   sfd         6.6         6.6     
BD200   gfgs233     5.0         9.9     
BD200   kj3244      4.9         9.9     
BD301   sdg33       3.1         3.1

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!

How do you choose what Feature 1 to keep? In the case of ID AB102 you are choosing jlkh234 over df87 — Angelo Canepa

Marc Pincince Marc Pincince · Accepted Answer · 2020-09-28T19:55:39

Just picking up where you began, with grouping after you filtered your rows, this would do it:

#"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID"}, {{"Feature2", each List.Sum([Feature2]), type nullable number}, {"AllData", each _, type table [ID=nullable text, Feature1=nullable text, Feature2=nullable number]}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Feature1"}, {"Feature1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"ID", "Feature1", "Feature2"})

All I did was set up the grouping like this:

Which gave me this:

Then I expanded the AllData column, only selecting Feature1 and not using the original column name as a prefix:

Which gave me this:

Then I dragged the Feature1 column to the left of the Feature2 column to get this:

Power Query: aggregation plus calculations

1 Answers