How to find the closest value within the same column-CodePudding

I am a newbie using Power Query, and started using PQ to rebuild whatever I can do in Excel. I wanted to find the closet value within the same column and then return the corresponding value from the other table.

Here's a visual that I hope it makes clear what I wanted to do. For example, I have 2 tables, the closet value to 29 is 30 so it will return the 30's corresponding value from other table, which is C.

In Excel I will use either way below to do the work.

=INDEX(L172:M174,MATCH(MIN(ABS(L172:L174-M176)),ABS(L172:L174-N171),0),2)

=VLOOKUP(INDEX(L172:L174,MATCH(MIN(ABS(L172:L174-M176)),ABS(L172:L174-N171),0)),L172:M174,2,FALSE)

In PQ, I only figured out how to look up the value from other table using the following function:

#"TABLE 2"[Category]{List.PositionOf(#"TABLE 2"[Value],Number.abs(xxx))}

But I am having a hard time approaching the returning the minimum absolute number part. I tried to use table.transformcolumns or list.transform, but either way gets me errors.

Thanks in advance!

CodePudding user response：

Assuming Table1 and Table2 are loaded into PowerQuery, the top part of this creates a function that finds the closest value in Table1, then pull row from Table2. The last few rows use that function on the input column Findclosest to find the closest value to each and every row

let
FC = (TestValue)=> let
// add an index to Table1 so we later can determine matching row
Source = Table.AddIndexColumn(Table1, "Index", 0, 1, Int64.Type),
//subtract the TestValue from each row in Table1, and take absolute number as result    
#"Added Custom" = Table.AddColumn(Source, "Diff", each Number.Abs([Column1]-TestValue)),
//sort results so that first row is closest match, first index in a close tie
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Diff", Order.Ascending},{"Index", Order.Ascending}}),
// pull the index number from the first row, which will be row we want to grab in Table2
Index = #"Sorted Rows"{0}[Index],
// pull the value from that row number in Table2, from column: Column1
Table2value=Table2{Index}[Column1]
in Table2value,

Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Findclosest", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each FC([Findclosest]))
in #"Added Custom"