Requesting help with MS Excel pivot table solution?

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.

 

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 https://www.microsoft.com/en-us/download/details.aspx?id=39379.

 

Hope that help

Haytham

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