Nearest value from another table with lookup criteria

[Syndicate_Admin]

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

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

Please try:

= 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]-[Sales Price]) else null),
"Deal Price")[Deal Price])

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...