# Power Query: Column and Row Lookup



## Pulsar3000 (Dec 14, 2022)

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.xlsxABCDEFG1LocationCustomerCustomer Risk0 - 30 Days31 - 60 Days61 - 90 Days90+ Days Old2NorthAdamLow$20,000$15,000$10,000$5,0003NorthJohnHigh$30,000$25,000$20,000$15,0004NorthDavidMedium$40,000$35,000$30,000$25,0005NorthAndrewLow$50,000$45,000$40,000$35,0006SouthSeanLow$60,000$55,000$50,000$45,0007SouthJosephHigh$70,000$65,000$60,000$55,0008SouthIshmaelMedium$80,000$75,000$70,000$65,0009SouthParkerLow$90,000$85,000$80,000$75,000Data
PQ Row Lookup.xlsxABCDEF1LocationRisk0 - 30 Days31 - 60 Days61 - 90 Days90+ Days Old2NorthLow0.50%0.75%1.00%1.25%3NorthMedium1.00%1.50%2.00%2.50%4NorthHigh2.00%2.50%3.00%3.50%5SouthLow0.70%0.95%1.20%1.45%6SouthMedium1.20%1.70%2.20%2.70%7SouthHigh2.20%2.70%3.20%3.70%Criteria


----------



## HongRu (Dec 14, 2022)

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 (Dec 14, 2022)

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 (Dec 14, 2022)

HongRu said:


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


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


----------



## HongRu (Dec 14, 2022)

Pulsar3000 said:


> 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 (Dec 15, 2022)

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.xlsxIJKLMNO1LocationCustomerCustomer Risk0 - 30 Days31 - 60 Days61 - 90 Days90+ Days Old2NorthAdamLow100112.510062.53NorthJohnHigh6006256005254NorthDavidMedium4005256006255NorthAndrewLow250337.5400437.56SouthSeanLow420522.5600652.57SouthJosephHigh15401755192020358SouthIshmaelMedium9601275154017559SouthParkerLow630807.59601087.5Data


----------



## Pulsar3000 (Dec 15, 2022)

Alex Blakenburg said:


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


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







Alex Blakenburg said:


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



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


----------



## Alex Blakenburg (Dec 15, 2022)

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


----------



## Pulsar3000 (Dec 15, 2022)

Alex Blakenburg said:


> 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 (Dec 15, 2022)

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?


----------



## Pulsar3000 (Dec 14, 2022)

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.xlsxABCDEFG1LocationCustomerCustomer Risk0 - 30 Days31 - 60 Days61 - 90 Days90+ Days Old2NorthAdamLow$20,000$15,000$10,000$5,0003NorthJohnHigh$30,000$25,000$20,000$15,0004NorthDavidMedium$40,000$35,000$30,000$25,0005NorthAndrewLow$50,000$45,000$40,000$35,0006SouthSeanLow$60,000$55,000$50,000$45,0007SouthJosephHigh$70,000$65,000$60,000$55,0008SouthIshmaelMedium$80,000$75,000$70,000$65,0009SouthParkerLow$90,000$85,000$80,000$75,000Data
PQ Row Lookup.xlsxABCDEF1LocationRisk0 - 30 Days31 - 60 Days61 - 90 Days90+ Days Old2NorthLow0.50%0.75%1.00%1.25%3NorthMedium1.00%1.50%2.00%2.50%4NorthHigh2.00%2.50%3.00%3.50%5SouthLow0.70%0.95%1.20%1.45%6SouthMedium1.20%1.70%2.20%2.70%7SouthHigh2.20%2.70%3.20%3.70%Criteria


----------



## Alex Blakenburg (Dec 15, 2022)

Pulsar3000 said:


> 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


----------

