Forum Discussion

mattyhuff's avatar
mattyhuff
Frequent Visitor
3 years ago
Solved

Nearest value from another table with lookup criteria

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

 

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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. 

7 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    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's avatar
      mattyhuff
      Frequent Visitor
      3 years ago

      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's avatar
        rubayatyasmin
        Community Champion
        3 years ago

        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's avatar
    mattyhuff
    Frequent Visitor
    3 years ago

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