Requesting help with MS Excel pivot table solution?

[Samuelson Anthony Sylim]

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

What I'm hoping to get from the Pivot table:
Tom L
Tom M
Tom N
Mark O
Mark P
Amy L
Amy M
Amy N
Paul Q
Paul R

Is this possible in Excel using Pivot Table or should I be using a different tool?

[#2]

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

[#3]

Hi Samuelson,

This can be done not in PivotTable but in Get & Transform in Excel 2016 (Aka Power Query add-in in earlier versions of Excel).

Please find the attached file to see it.

NOTE: If you don't have Excel 2016, you have to download and install the Power Query add-in.

Hope that help

Haytham

[#4]

Hi

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.