Forum Discussion

bartvana's avatar
bartvana
Iron Contributor
Mar 30, 2022
Solved

Power Query Grouping and retaining other columns

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, in...
BI & Data Analysis
power bi
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 30, 2022

    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.

     

     

     

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 30, 2022

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.

 

 

 

 

Resources