Log In
Register
Home
Power Query
Power Pivot
July 11, 2016
Offline
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
VIP
Trusted Members
December 7, 2016
OfflinePower Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
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
Power Query
Power Pivot
July 11, 2016
Offline
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
November 8, 2013
Offline
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)"]
1 Guest(s)
