Nearest value from another table with lookup criteria

[mattyhuff]

I have two tables:

Table 1 is pricing information with columns Pricing Group and Deal Price

Table 2 is sales information with columns Pricing Group and Sales Price

I'm trying to figure out the power query code to create a column in Table 2 that finds the nearest value Deal Price from Table 1 compared to the Sales Price in Table 2 where Table1.[Pricing Group] = Table2.[Pricing Group]. 

I've scrapped together the following code to try and find the minimum absolute value. 

= Table.AddColumn(Source, "Nearest Price",
(r) => Table.Min(
Table.SelectRows(
Table.Distinct(Table.SelectColumns(#"RNDC IN Pricing ALL",{"Pricing Group", "Deal Price"})),
each if r[Pricing Group] = [Pricing Group] then Number.Abs([Deal Price]-[Price]) else null),
"Deal Price")[Deal Price])

Any help would be much appreciated.

Would prefer not to use DAX

[mattyhuff]

I got it to work but it takes forever to run especially with 10K+ lines of data. Thank you for your help.

[rubayatyasmin]

Happy to help. 

Appreciate the kudos. 👍

[rubayatyasmin]

Hi, @mattyhuff 

good attempt on this , here is the reviewed version,

let
Source = Table2, // Assuming you have already loaded Table2 as Source
DistinctPricing = Table.Distinct(Table.SelectColumns(Table1,{"Pricing Group", "Deal Price"})),

FindNearestPrice = (r) =>
let
RelevantPrices = Table.SelectRows(DistinctPricing, each r[Pricing Group] = [Pricing Group]),
Differences = Table.AddColumn(RelevantPrices, "Difference", each Number.Abs([Deal Price]-r[Sales Price])),
MinDifference = List.Min(Differences[Difference]),
NearestRow = Table.SelectRows(Differences, each [Difference] = MinDifference){0},
NearestValue = NearestRow[Deal Price]
in
NearestValue,

WithNearestPrice = Table.AddColumn(Source, "Nearest Price", each FindNearestPrice(_))

in
WithNearestPrice

Note: This code assumes that there is at least one matching 'Pricing Group' between the tables. If there's a possibility that Table2 might have 'Pricing Groups' not found in Table1, you'll need to add additional error handling.

[mattyhuff]

This seemed to have worked but I'm running into an issue where it's trying to load a table into a table. My original source has some extra steps. I've attached the code:

let
Source = Excel.Workbook(Web.Contents("Rolling 12 Month Sales.xlsx"), null, true),
#"Rolling 12 Month Sales_Sheet" = Source{[Item="Rolling 12 Month Sales",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Rolling 12 Month Sales_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Beverage Type", type text},{"Pricing Group", type text}, {"Column3", Int64.Type}, {"Price", type number}, {"STD. Cases (Rolling 12 Months)", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column3", "Pricing Group ID"}, {"Pricing Group", "Pricing Group Name"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Pricing Group", each [Pricing Group Name] & " (" & Number.ToText([Pricing Group ID]) & ")"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Pricing Group", "Pricing Group Name", "Pricing Group ID", "Price", "STD. Cases (Rolling 12 Months)"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Pricing Group] <> "DELETED ITEMS (5)" and [Pricing Group] <> "UNASSIGNED (0)") and ([Beverage Type] = "WINES" or [Beverage Type] = "BEERS")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Pricing Group Name", "Pricing Group ID"}),

#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Nearest Price", each let
DistinctPricing=Table.Distinct(Table.SelectColumns(#"RNDC IN Pricing ALL",{"Pricing Group","Deal Price"})),

FindNearestPrice = (r) =>
let
RelevantPrices = Table.SelectRows(DistinctPricing, each r[Pricing Group]=[Pricing Group]),
Differences = Table.AddColumn(RelevantPrices, "Difference", each Number.Abs([Deal Price]-r[Price])),
MinDifference = List.Min(Differences[Difference]),
NearestRow = Table.SelectRows(Differences, each [Difference]= MinDifference){0},
NearestValue = NearestRow[Deal Price]

in
NearestValue,
WithNearestPrice = Table.AddColumn(#"Removed Columns1","Nearest Price", each FindNearestPrice(_))

in
WithNearestPrice)
in
#"Added Custom1"

[rubayatyasmin]

It seems like you're encountering an error because the table reference #"RNDC IN Pricing ALL"is not defined in the provided code. To fix this issue, you need to make sure that the reference toTable1` (Pricing information) is correct.

DistinctPricing=Table.Distinct(Table.SelectColumns(#"RNDC IN Pricing ALL",{"Pricing Group","Deal Price"})),

adjust this line

[mattyhuff]

Table 1 is actually called RNDC IN Pricing All so I believe the reference is correct. Let me know if anything else in the code is incorrect.

[rubayatyasmin · Accepted solution]

But where is the query step that defines #RUNDC?

You are using it as previous step. PQ evaluates every step. 

Try removing the #rundc. 

It should work. 

I am seeing any problem. 

If my assistance helped you in any way, hit 👍

accept it as a solution.