Nearest value from another table with lookup criteria

(0) Share

Report

Posted on by 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