Forum Discussion
Nearest value from another table with lookup criteria
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.
Community ChampionHi, 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.
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
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
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.