Forum Discussion

Samuelson Anthony Sylim's avatar
Samuelson Anthony Sylim
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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 06, 2018

Hi,

 

The easiest way is to use Power Query (aka Get & Transform). If you have two source tables like here

resulting table (on the right) is generated by few clicks, resulting script on loaded source tables is

let
    Source = Table.NestedJoin(Table1,{"Letter"},Table2,{"Letter"},"Table2",JoinKind.LeftOuter),
    ExpandTable = Table.ExpandTableColumn(Source, "Table2", {"Map"}, {"Map"}),
    RemoveLetter = Table.RemoveColumns(ExpandTable,{"Letter"})
in
    RemoveLetter

 

Resources