Forum Discussion

Copper Contributor

Apr 06, 2018

Requesting help with MS Excel pivot table solution?

I hope someone can help me with this. Say, I have two tables in excel: Table1: Tom A Mark B Amy A Paul C Table2: A L A M A N B O B P C Q C R W...

Formulas & Functions

tips and tricks

Detlef_Lewin

Silver Contributor

Apr 06, 2018

I would rather use M-Code and not Pivot.

Import Table1 and Table2 into the query editor and close and load them as connection only.

Then merge both as a new query, expand Col_2 and remove Col_1.

let
    Source = Table.NestedJoin(Table1,{"Col_1"},Table2,{"Col_1"},"Table2",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "Table2", {"Col_2"}, {"Col_2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Col_1"})
in
    #"Removed Columns"

Edit:

I was to slow.