How to nest If in Functions

[Mic1979]

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 this, but I thought it was good to share.

I have implemented it in my table:

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

C = Table.FromColumns (List.Transform (
     A,
     each List.ReplaceMatchingItems (
     _,
     B,
     Comparer.OrdinalIgnoreCase
          )
     ),
     Table.ColumnNames (#"Removed Columns2")
)

It works.

However, I need to use this only if a special condition is verified.

Could you suggest how to include the if...then...else here?

In general, I have always problems when I need to add the if in the functions.

Thanks a lot for your help in advance.

[v-sdhruv]

Hi @Mic1979 

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Mic1979 

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

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!

[FreemanZ]

hi @Mic1979 ,

I would suggest you check the following:

https://goodly.co.in/if-function-power-query/

https://www.youtube.com/watch?v=HQ4RNJiVurI

https://gorilla.bi/power-query/if-function-in-power-query/

[grognard]

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 🙂