Power Query: Column and Row Lookup

[Pulsar3000]

Hello All:

Hope you can all help with my task at hand.
I want to multiply the percentage from the "Criteria" tab that matches the dollar amount for the respective Location, Customer Risk, and Days Old in the "Data" tab.
For example, the corresponding percentage from the "Criteria" tab for cell D3 in the "Data" tab is 2%.
I then want to take this 2% and multiply it by the amount in cell D3 which has $30,000.

I know merge queries in Power Query allows for matching columns and in my case this would work for the Location and Risk column.
However, how do I do the three-way match in Power Query when the the "days old" item is in a row?

PQ Row Lookup.xlsx
	A	B	C	D	E	F	G
1	Location	Customer	Customer Risk	0 - 30 Days	31 - 60 Days	61 - 90 Days	90+ Days Old
2	North	Adam	Low	$20,000	$15,000	$10,000	$5,000
3	North	John	High	$30,000	$25,000	$20,000	$15,000
4	North	David	Medium	$40,000	$35,000	$30,000	$25,000
5	North	Andrew	Low	$50,000	$45,000	$40,000	$35,000
6	South	Sean	Low	$60,000	$55,000	$50,000	$45,000
7	South	Joseph	High	$70,000	$65,000	$60,000	$55,000
8	South	Ishmael	Medium	$80,000	$75,000	$70,000	$65,000
9	South	Parker	Low	$90,000	$85,000	$80,000	$75,000
Data

PQ Row Lookup.xlsx
	A	B	C	D	E	F
1	Location	Risk	0 - 30 Days	31 - 60 Days	61 - 90 Days	90+ Days Old
2	North	Low	0.50%	0.75%	1.00%	1.25%
3	North	Medium	1.00%	1.50%	2.00%	2.50%
4	North	High	2.00%	2.50%	3.00%	3.50%
5	South	Low	0.70%	0.95%	1.20%	1.45%
6	South	Medium	1.20%	1.70%	2.20%	2.70%
7	South	High	2.20%	2.70%	3.20%	3.70%
Criteria

 

[HongRu]

try this.

Put the below formula in a cell you want, then copy it to right and downward.

=SUMPRODUCT((Criteria!$A$2:$A$7=Data!$A2)*(Criteria!$B$2:$B$7=Data!$C2)*Criteria!C$2:C$7)*Data!D2

 

[HongRu]

or this

=SUMIFS(Criteria!C$2:C$7,Criteria!$A$2:$A$7,Data!$A2,Criteria!$B$2:$B$7,Data!$C2)*Data!D2

 

[Pulsar3000]

try this.

Put the below formula in a cell you want, then copy it to right and downward.

=SUMPRODUCT((Criteria!$A$2:$A$7=Data!$A2)*(Criteria!$B$2:$B$7=Data!$C2)*Criteria!C$2:C$7)*Data!D2

Not sure if I misunderstood your response, but I'm looking for a Power Query solution.

 

[HongRu]

Not sure if I misunderstood your response, but I'm looking for a Power Query solution.

You are right.
Mine is not a PQ solution.

 

[Alex Blakenburg]

Let me know how you want to do this.
My Table names are:
• tblCustomers
• tblPerCent

If you use those names you can create 2 blank queries and post in the code below OR load each one into PQ and then replace the code for each with the code below.
(Ideally do query 2 below first)

1) Query - tblCustomersCalc - Close and Load to Worksheet

let
    Source = Excel.CurrentWorkbook(){[Name="tblCustomers"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Location", "Customer", "Customer Risk", "Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Location", type text}, {"Customer", type text}, {"Customer Risk", type text}, {"Attribute", type text}, {"Value", Currency.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Location", "Customer Risk", "Attribute"}, tblPerCent, {"Location", "Risk", "Attribute"}, "tblPerCent", JoinKind.LeftOuter),
    #"Expanded tblPerCent" = Table.ExpandTableColumn(#"Merged Queries", "tblPerCent", {"Value"}, {"Value.1"}),
    RenamedColumnsPerCent = Table.RenameColumns(#"Expanded tblPerCent",{{"Value.1", "PerCent"}}),
    #"Inserted Multiplication" = Table.AddColumn(RenamedColumnsPerCent, "Multiplication", each [Value] * [PerCent], Currency.Type),
    RoundingNormal = Table.TransformColumns(#"Inserted Multiplication",{{"Multiplication", each Number.Round(_, 2), Currency.Type}}),
    RenamedColumnsCalc = Table.RenameColumns(RoundingNormal,{{"Multiplication", "Calculated Amt"}}),
    RemovedColumnsOtherValues = Table.RemoveColumns(RenamedColumnsCalc,{"Value", "PerCent"}),
    #"Pivoted Column" = Table.Pivot(RemovedColumnsOtherValues, List.Distinct(RemovedColumnsOtherValues[Attribute]), "Attribute", "Calculated Amt", List.Sum),
    SortedRowsOrigOrder = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    RemovedColumnsIndex = Table.RemoveColumns(SortedRowsOrigOrder,{"Index"})
in
    RemovedColumnsIndex

2) Query - tblPerCent - Close and Load Connection Only

let
    Source = Excel.CurrentWorkbook(){[Name="tblPerCent"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location", "Risk"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Percentage.Type}, {"Location", type text}, {"Risk", type text}})
in
    #"Changed Type"

Result

20221215 PQ Multiply Columns Rows Pulsar3000.xlsx
	I	J	K	L	M	N	O
1	Location	Customer	Customer Risk	0 - 30 Days	31 - 60 Days	61 - 90 Days	90+ Days Old
2	North	Adam	Low	100	112.5	100	62.5
3	North	John	High	600	625	600	525
4	North	David	Medium	400	525	600	625
5	North	Andrew	Low	250	337.5	400	437.5
6	South	Sean	Low	420	522.5	600	652.5
7	South	Joseph	High	1540	1755	1920	2035
8	South	Ishmael	Medium	960	1275	1540	1755
9	South	Parker	Low	630	807.5	960	1087.5
Data

 

[Pulsar3000]

Let me know how you want to do this.
My Table names are:
• tblCustomers
• tblPerCent

If you use those names you can create 2 blank queries and post in the code below OR load each one into PQ and then replace the code for each with the code below.
(Ideally do query 2 below first)

1) Query - tblCustomersCalc - Close and Load to Worksheet

let
    Source = Excel.CurrentWorkbook(){[Name="tblCustomers"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Location", "Customer", "Customer Risk", "Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Location", type text}, {"Customer", type text}, {"Customer Risk", type text}, {"Attribute", type text}, {"Value", Currency.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Location", "Customer Risk", "Attribute"}, tblPerCent, {"Location", "Risk", "Attribute"}, "tblPerCent", JoinKind.LeftOuter),
    #"Expanded tblPerCent" = Table.ExpandTableColumn(#"Merged Queries", "tblPerCent", {"Value"}, {"Value.1"}),
    RenamedColumnsPerCent = Table.RenameColumns(#"Expanded tblPerCent",{{"Value.1", "PerCent"}}),
    #"Inserted Multiplication" = Table.AddColumn(RenamedColumnsPerCent, "Multiplication", each [Value] * [PerCent], Currency.Type),
    RoundingNormal = Table.TransformColumns(#"Inserted Multiplication",{{"Multiplication", each Number.Round(_, 2), Currency.Type}}),
    RenamedColumnsCalc = Table.RenameColumns(RoundingNormal,{{"Multiplication", "Calculated Amt"}}),
    RemovedColumnsOtherValues = Table.RemoveColumns(RenamedColumnsCalc,{"Value", "PerCent"}),
    #"Pivoted Column" = Table.Pivot(RemovedColumnsOtherValues, List.Distinct(RemovedColumnsOtherValues[Attribute]), "Attribute", "Calculated Amt", List.Sum),
    SortedRowsOrigOrder = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    RemovedColumnsIndex = Table.RemoveColumns(SortedRowsOrigOrder,{"Index"})
in
    RemovedColumnsIndex

2) Query - tblPerCent - Close and Load Connection Only

let
    Source = Excel.CurrentWorkbook(){[Name="tblPerCent"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location", "Risk"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Percentage.Type}, {"Location", type text}, {"Risk", type text}})
in
    #"Changed Type"

Result

20221215 PQ Multiply Columns Rows Pulsar3000.xlsx
	I	J	K	L	M	N	O
1	Location	Customer	Customer Risk	0 - 30 Days	31 - 60 Days	61 - 90 Days	90+ Days Old
2	North	Adam	Low	100	112.5	100	62.5
3	North	John	High	600	625	600	525
4	North	David	Medium	400	525	600	625
5	North	Andrew	Low	250	337.5	400	437.5
6	South	Sean	Low	420	522.5	600	652.5
7	South	Joseph	High	1540	1755	1920	2035
8	South	Ishmael	Medium	960	1275	1540	1755
9	South	Parker	Low	630	807.5	960	1087.5
Data

Let me know how you want to do this.
My Table names are:
• tblCustomers
• tblPerCent

If you use those names you can create 2 blank queries and post in the code below OR load each one into PQ and then replace the code for each with the code below.
(Ideally do query 2 below first)

1) Query - tblCustomersCalc - Close and Load to Worksheet

let
    Source = Excel.CurrentWorkbook(){[Name="tblCustomers"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Location", "Customer", "Customer Risk", "Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Location", type text}, {"Customer", type text}, {"Customer Risk", type text}, {"Attribute", type text}, {"Value", Currency.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Location", "Customer Risk", "Attribute"}, tblPerCent, {"Location", "Risk", "Attribute"}, "tblPerCent", JoinKind.LeftOuter),
    #"Expanded tblPerCent" = Table.ExpandTableColumn(#"Merged Queries", "tblPerCent", {"Value"}, {"Value.1"}),
    RenamedColumnsPerCent = Table.RenameColumns(#"Expanded tblPerCent",{{"Value.1", "PerCent"}}),
    #"Inserted Multiplication" = Table.AddColumn(RenamedColumnsPerCent, "Multiplication", each [Value] * [PerCent], Currency.Type),
    RoundingNormal = Table.TransformColumns(#"Inserted Multiplication",{{"Multiplication", each Number.Round(_, 2), Currency.Type}}),
    RenamedColumnsCalc = Table.RenameColumns(RoundingNormal,{{"Multiplication", "Calculated Amt"}}),
    RemovedColumnsOtherValues = Table.RemoveColumns(RenamedColumnsCalc,{"Value", "PerCent"}),
    #"Pivoted Column" = Table.Pivot(RemovedColumnsOtherValues, List.Distinct(RemovedColumnsOtherValues[Attribute]), "Attribute", "Calculated Amt", List.Sum),
    SortedRowsOrigOrder = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    RemovedColumnsIndex = Table.RemoveColumns(SortedRowsOrigOrder,{"Index"})
in
    RemovedColumnsIndex

2) Query - tblPerCent - Close and Load Connection Only

let
    Source = Excel.CurrentWorkbook(){[Name="tblPerCent"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location", "Risk"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Percentage.Type}, {"Location", type text}, {"Risk", type text}})
in
    #"Changed Type"

Result

20221215 PQ Multiply Columns Rows Pulsar3000.xlsx
	I	J	K	L	M	N	O
1	Location	Customer	Customer Risk	0 - 30 Days	31 - 60 Days	61 - 90 Days	90+ Days Old
2	North	Adam	Low	100	112.5	100	62.5
3	North	John	High	600	625	600	525
4	North	David	Medium	400	525	600	625
5	North	Andrew	Low	250	337.5	400	437.5
6	South	Sean	Low	420	522.5	600	652.5
7	South	Joseph	High	1540	1755	1920	2035
8	South	Ishmael	Medium	960	1275	1540	1755
9	South	Parker	Low	630	807.5	960	1087.5
Data

Thank You! I went ahead and copied the queries.
I get the below error. Any idea how to fix?

Let me know how you want to do this.
My Table names are:
• tblCustomers
• tblPerCent

If you use those names you can create 2 blank queries and post in the code below OR load each one into PQ and then replace the code for each with the code below.
(Ideally do query 2 below first)

1) Query - tblCustomersCalc - Close and Load to Worksheet

let
    Source = Excel.CurrentWorkbook(){[Name="tblCustomers"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Location", "Customer", "Customer Risk", "Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Location", type text}, {"Customer", type text}, {"Customer Risk", type text}, {"Attribute", type text}, {"Value", Currency.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Location", "Customer Risk", "Attribute"}, tblPerCent, {"Location", "Risk", "Attribute"}, "tblPerCent", JoinKind.LeftOuter),
    #"Expanded tblPerCent" = Table.ExpandTableColumn(#"Merged Queries", "tblPerCent", {"Value"}, {"Value.1"}),
    RenamedColumnsPerCent = Table.RenameColumns(#"Expanded tblPerCent",{{"Value.1", "PerCent"}}),
    #"Inserted Multiplication" = Table.AddColumn(RenamedColumnsPerCent, "Multiplication", each [Value] * [PerCent], Currency.Type),
    RoundingNormal = Table.TransformColumns(#"Inserted Multiplication",{{"Multiplication", each Number.Round(_, 2), Currency.Type}}),
    RenamedColumnsCalc = Table.RenameColumns(RoundingNormal,{{"Multiplication", "Calculated Amt"}}),
    RemovedColumnsOtherValues = Table.RemoveColumns(RenamedColumnsCalc,{"Value", "PerCent"}),
    #"Pivoted Column" = Table.Pivot(RemovedColumnsOtherValues, List.Distinct(RemovedColumnsOtherValues[Attribute]), "Attribute", "Calculated Amt", List.Sum),
    SortedRowsOrigOrder = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    RemovedColumnsIndex = Table.RemoveColumns(SortedRowsOrigOrder,{"Index"})
in
    RemovedColumnsIndex

2) Query - tblPerCent - Close and Load Connection Only

let
    Source = Excel.CurrentWorkbook(){[Name="tblPerCent"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location", "Risk"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Percentage.Type}, {"Location", type text}, {"Risk", type text}})
in
    #"Changed Type"

Result

20221215 PQ Multiply Columns Rows Pulsar3000.xlsx
	I	J	K	L	M	N	O
1	Location	Customer	Customer Risk	0 - 30 Days	31 - 60 Days	61 - 90 Days	90+ Days Old
2	North	Adam	Low	100	112.5	100	62.5
3	North	John	High	600	625	600	525
4	North	David	Medium	400	525	600	625
5	North	Andrew	Low	250	337.5	400	437.5
6	South	Sean	Low	420	522.5	600	652.5
7	South	Joseph	High	1540	1755	1920	2035
8	South	Ishmael	Medium	960	1275	1540	1755
9	South	Parker	Low	630	807.5	960	1087.5
Data

Thank You! I went ahead and copied the queries.
I get the below error. Any idea on how to fix it?

 

[Alex Blakenburg]

Have you made both data ranges actual Excel Tables ?
Have you given them the same names that I have per my previous post ?

 

[Pulsar3000]

Have you made both data ranges actual Excel Tables ?
Have you given them the same names that I have per my previous post ?

I fixed a table name. It worked!
Thank you so much!!

 

[Pulsar3000]

The solution in Power Query is to convert the data in rows into columns by unpivoting and then pivoting back.
So basically we can't do a row lookup in Power Query?