PQ - VLOOKUP on Matching Multiple Criteria

[sandy666]

The solution is for a thread VLOOKUP-on-Matching-Multiple-Criteria

Source						Result
Sales Man	Territory	Dimension	Sales Amt	Cost		Sales Man	Territory	Dimension	Sales	Cost
John	New York	Tissue	1000.00	200.00		Maxwell	Washington	Towel	1000	800
Alfred	Washington	Soda	2100.00	700.00		Maxwell	Washington	Tissue
John	New York	Soda	2050.00	1500.00		Maxwell	Washington	Paper
Alfred	New York	Tissue	2000.00	500.00		Maxwell	Washington	Soda
Leo	Washington	Soda	200.00	100.00		Maxwell	Coburg	Paper
Leo	New York	Tissue	3500.00	1500.00		Maxwell	Coburg	Towel
Maxwell	Washington	Towel	1000.00	800.00		Maxwell	Coburg	Tissue
Maxwell	Coburg					Maxwell	Coburg	Soda
						Leo	New York	Tissue	3500	1500
						Leo	Washington	Soda	200	100
						Leo	Washington	Tissue
						Leo	Washington	Towel
						Leo	Washington	Paper
						Leo	New York	Towel
						Leo	New York	Paper
						Leo	New York	Soda
						John	New York	Soda	2050	1500
						John	New York	Tissue	1000	200
						John	New York	Tissue
						John	New York	Paper
						John	New York	Towel
						John	New York	Paper
						John	New York	Soda
						John	New York	Towel
						Alfred	Washington	Soda	2100	700
						Alfred	New York	Tissue	2000	500
						Alfred	New York	Towel
						Alfred	Washington	Tissue
						Alfred	New York	Soda
						Alfred	New York	Paper
						Alfred	Washington	Paper
						Alfred	Washington	Towel

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