Where can I add a conditional statement in this M function code (Power Query)?

Question

I'm trying to work my way through this PowerQuery problem but I'm getting fairly stumped (been throwing myself at it for hours).

The goal is to retrieve a value from a table, closest to a variable (x). Sample of the table:

S	1	2	3
0	1698	1737	1781
1	1737	1795	1855
2	1780	1854	1928
3	1822	1912	2002
4	1864	1971	2075
5	1907	2029	2149
6	1949	2086	2222
7	1992	2145	2296
8	2034	2203	2369
9	2077	2262	2443
10	2119	2320	2516
11	2162	2378	2590

Let's say the variable S = "1" (column name) and variable x is "2000". The code I'm using works fine in this scenario:

    let

    S = "1",
    x = 2000,
    Source = tblSchalen,
    Result = Table.Column(Source, S){List.Count(List.Select( Table.Column(Source, S), each _ <= x))}
in
    Result

This correctly returns the value of "2034", as 2000 is higher than 1992 and the closest (upstairs) neighbour is 2034.

The problem is that, if I make x "2500" it errors (too few elements in enumeration) since the search procedure sort of "overflows". I vaguely understand why this happens (it's counting the amount (List.Select) of values that are lower or equal to X, and uses that number that to return a row number?) but I'd like to prevent this overflow by inserting some conditional statements as I just would want every x variable above the highest value in the list to just return the highest value in the list. (so x = 2500 would return "2162" if S is 1, "2378" if S is 2 and "2516" if S is 3)

Could anyone help me in the right direction? The syntax in M is different enough from VBA to make me confused and the M editor isn't quite as helpful as the VBA editor when debugging.

edit:

I guess I "want something like this, but working", these conditional statements in M really trip me up, as the code below gives me a "Token Identifyer Expected" error:

    let
    S = "1",
    x = 2500,
    Source = tblSchalen,    
    amount = List.Count(List.Select(Table.Column(Source, S), each _ <= x)), // this return a number from 0 - 12
    if amount > 11 then amount = 11 else amount = amount
    Result = Table.Column(amount)
in
    Result

I've tried "Result = if amount > 11 etc etc" just to give it some identifier, but that's also no bueno.

user_9264230 · Answer

This would find the closest value to Table2 Column1 from Table1, using column name listed in Table2 Column2

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
TransformedTable1=Table.Buffer(Table.UnpivotOtherColumns(Table1, {"S"}, "Attribute", "Value")),
#"Added Custom" = Table.AddColumn(#"Changed Type","Closest",(i)=>
Table.Sort(
    Table.AddColumn(Table.SelectRows(TransformedTable1,
    each [Attribute]=Text.From(i[Column2])),
   "diff", each Number.Abs([Value]-i[Column1]))
,{{"diff", Order.Ascending}})
{0}[Value]
)
in #"Added Custom"

Note: Column 2 is the name of the column, not the position

Where can I add a conditional statement in this M function code (Power Query)?

1 Answers