Forum Discussion

Mic1979's avatar
Mic1979
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

How to nest If in Functions

Dear all,   I found a very interesting video about  replacement values in a table. Formula E | Electrified, Hankook iON x Formula E Technology (30s) | HankookTire Probably most of you already saw...
Need Help
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    Hi Mic1979 

    You are quite close-
    You need to use nested if...else inside List.Transform and  make sure if is inside each  (i.e., per column list), not outside it. You can modify the syntax to something like this-

    A = Table.ToColumns(#"Removed Columns2"),
    B = Table.ToRows(ReplacementTable_Body_StuffingBox1),

    C = Table.FromColumns(
    List.Transform(
    A,
    each
    if condition1 then
    List.ReplaceMatchingItems(_, B, Comparer.OrdinalIgnoreCase)
    else if condition2 then
    List.ReplaceMatchingItems(_, B, Comparer.Ordinal)
    else
    _
    ),
    Table.ColumnNames(#"Removed Columns2")
    )
    Hope this helps!

grognard's avatar
grognard
Icon for Helper I rankHelper I
1 year ago

Hello

I think this is what you are looking for:

  A = Table.ToColumns (#"Removed Columns2"),
    B = Table.ToRows (ReplacementTable_Body_StuffingBox1),
    
    C = Table.FromColumns( 
    List.Transform(
        A, 
        each if condition then List.ReplaceMatchingItems(_,B,Comparer.OrdinalIgnoreCase) 
        else _
        ),
        Table.ColumnNames(#"Removed Columns2")
    )

just replace the 'condition' with the actual condition you are looking for and you should be good.

You can also nest if functions by chaining them together like so:

A = Table.ToColumns (#"Removed Columns2"),
    B = Table.ToRows (ReplacementTable_Body_StuffingBox1),
    
    C = Table.FromColumns( 
    List.Transform(
        A, 
        each
        //remember that the first condition that evaluates to true will result in the function returning.
        if condition1 then List.ReplaceMatchingItems(_,B,Comparer.OrdinalIgnoreCase) else
        if condition2 then List.ReplaceMatchingItems(_,B,Comparer.Ordinal)
        else _
        ),
        Table.ColumnNames(#"Removed Columns2")
    )

hope this helps 🙂