Power Query
Power Pivot
July 11, 2016
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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)