PQ - VLOOKUP on Matching Multiple Criteria

[sandy666]

The solution is for a thread VLOOKUP-on-Matching-Multiple-Criteria (http://www.excelfox.com/forum/showthread.php/2667-VLOOKUP-on-Matching-Multiple-Criteria?p=15025&viewfull=1#post15025)


SourceResult

Sales ManTerritoryDimension Sales Amt Cost Sales ManTerritoryDimensionSalesCost

JohnNew YorkTissue
1000.00
200.00MaxwellWashingtonTowel
1000
800

AlfredWashingtonSoda
2100.00
700.00MaxwellWashingtonTissue

JohnNew YorkSoda
2050.00
1500.00MaxwellWashingtonPaper

AlfredNew YorkTissue
2000.00
500.00MaxwellWashingtonSoda

LeoWashingtonSoda
200.00
100.00MaxwellCoburgPaper

LeoNew YorkTissue
3500.00
1500.00MaxwellCoburgTowel

MaxwellWashingtonTowel
1000.00
800.00MaxwellCoburgTissue

MaxwellCoburgMaxwellCoburgSoda

LeoNew YorkTissue
3500
1500

LeoWashingtonSoda
200
100

LeoWashingtonTissue

LeoWashingtonTowel

LeoWashingtonPaper

LeoNew YorkTowel

LeoNew YorkPaper

LeoNew YorkSoda

JohnNew YorkSoda
2050
1500

JohnNew YorkTissue
1000
200

JohnNew YorkTissue

JohnNew YorkPaper

JohnNew YorkTowel

JohnNew YorkPaper

JohnNew YorkSoda

JohnNew YorkTowel

AlfredWashingtonSoda
2100
700

AlfredNew YorkTissue
2000
500

AlfredNew YorkTowel

AlfredWashingtonTissue

AlfredNew YorkSoda

AlfredNew YorkPaper

AlfredWashingtonPaper

AlfredWashingtonTowel



let
Dimension = {"Tissue" , "Soda" , "Paper" , "Towel"},
C2T = Table.FromList(Dimension, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Table = Table.AddColumn(Source, "Custom", each C2T),
Expand = Table.ExpandTableColumn(Table, "Custom", {"Column1"}, {"Column1"}),
IF1 = Table.AddColumn(Expand, "Custom", each if [Dimension] = [Column1] then [Sales Amt] else null),
IF2 = Table.AddColumn(IF1, "Custom.1", each if [Dimension] = [Column1] then [Cost] else null),
Reorder = Table.ReorderColumns(IF2,{"Sales Man", "Territory", "Column1", "Dimension", "Sales Amt", "Cost", "Custom", "Custom.1"}),
RC = Table.RemoveColumns(Reorder,{"Dimension", "Sales Amt", "Cost"}),
Ren = Table.RenameColumns(RC,{{"Custom", "Sales"}, {"Custom.1", "Cost"}, {"Column1", "Dimension"}}),
Sort = Table.Sort(Ren,{{"Sales Man", Order.Descending}, {"Sales", Order.Descending}})
in
Sort