Forum Discussion
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 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.
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!
6 Replies
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 🙂
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/
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!
