Hi,
Is there a way to add the nearest matching value to a row similar to Excel VLOOKUP approximate match?
I have a data set that shows the weights and dimensions of products. In some instances the weight is missing but I have another table that shows an average weight based on the products diagonal size. I want to populate the missing weight fields with the average one using the diagonal size which is a field that exists in both tables. However the diagonal size values do not match exactly so I want to pick up the weight from the nearest diagonal size.
For example in the data set I have an item with no weight where the diagonal size is 22 inches. In the average weight table it shows that the average weight for a 20 inch product is 5kg and for a 30 inch product it is 10kg. I want to pick up the weight of the nearest matching item so in this instance it would be 5kg.
I have attached a file showing some example data.
Thanks
Bax
Hi Anders,
File attached now has a query added. I want to somehow combine the two queries in the file to fill in the missing weights in the Query_data table.
Thanks
Bax
Hi baxbax,
you can use the following function, that replicates the nearest match lookup:
(Parameter)=>
let
Source = Excel.CurrentWorkbook(){[Name="Table_Lookup"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Diagonal (in)", type number}, {"Weight (kg)", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Diagonal (in)", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [#"Diagonal (in)"] <= Parameter),
Group = #"Filtered Rows"{0}[#"Weight (kg)"]
in
Group
Hi Catalin,
Your solution works really well. Do you have any explanation as to how this works as I would like to understand it a bit more so I can use in future.
Thanks
Bax
The function is selecting only the rows from the lookup table where the Diagonal is smaller than the parameter passed to this function:
Table.SelectRows(#"Sorted Rows", each [#"Diagonal (in)"] <= Parameter),
Because this table is already sorted descending, the very first row will contain our needed value: ({0} means first row, it's zero based)
Group = #"Filtered Rows"{0}[#"Weight (kg)"]
Hi Catalin,
Thanks for taking the time to add the explanation. It really helps.
Bax
