Power Query Grouping and retaining other columns

[bartvana]

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:

Many thanks in advance!

[#2 · Accepted 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.

[#3]

@Riny_van_Eekelen Perfect, thank you!