SOLVED

Power Query Grouping and retaining other columns

Frequent Contributor

I have two similar, common use cases in Power Query for which I struggle to find the easiest solution or best practice. (Easy, for me, meaning: using the Power Query UI options if at all possible, instead of direct M coding. If not possible, then editing in the Advanced Editor is OK also.)

See workbook attached.

Use case 1 is filtering a table on the Max value of a column, per group.

Use case 2 is grouping by sum (or other aggregation), and retaining other columns also (except from the Group By, and aggregated columns). Like so:

bartvana_0-1648621269938.png

Many thanks in advance!

2 Replies
best response confirmed by bartvana (Frequent Contributor)
Solution

@bartvana See attached.

Result2 is a UI only solution. No M-code edits required.

 

Result1 requires one small M-code edit. After grouping by the first three columns, merge the query with itself and edit the M-code from:

 

= Table.NestedJoin(#"Grouped Rows", {"Product", "Data1", "Data2", "Value"}, #"Grouped Rows", {"Product", "Data1", "Data2", "Value"}, "Grouped Rows", JoinKind.LeftOuter)

 

to:

= Table.NestedJoin(#"Grouped Rows", {"Product", "Data1", "Data2", "Value"}, Source, {"Product", "Data1", "Data2", "Value"}, "Grouped Rows", JoinKind.LeftOuter)

 

All other steps are done by clicking and dragging in the UI.