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

 

[Alex Blakenburg]

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?

You can but then you have the issue of having to multiply each column individually eg Customer 0-30 x PerCent 0-30, Customer 31-60 x PerCent 31-60.
Doing it that way would also mean you will have hardcoded the aging columns, the way it is currently, if you change the aging grouping it would still work.

I have only used the User Interface to build it, if you are prepared to do more using M, then I am sure there will other ways of doing it