CODEWITHSUNDEEP
excelpowerbipowerquerym
Sardientje
Sardientje

Reputation: 11

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

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.

Upvotes: 1

Views: 494

Answers (1)

horseyride
horseyride

Reputation: 21318

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"

enter image description here

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

Upvotes: 0

Related Questions