Forum Discussion
How to replace OR condition in power query M
Hiya all,
I am working with a piece of code as follows :
Table.AddColumn(#"Rounded Off", "Active", each if Text.Contains([MATERIAL DESC],"GOLD") or Text.Contains([MATERIAL DESC],"GLD")
then "Aspertame" else
if Text.Contains([MATERIAL DESC],"NATURA")
then "Sucralose" else
if Text.Contains([MATERIAL DESC],"GREEN") or Text.Contains([MATERIAL DESC],"GRN")
then "Stevia" else
if Text.Contains([MATERIAL DESC],"SUGARLITE") then "Half-Sugar"
else "Not Relevant"
)Is there a way I can use something like the IN operator in Dax - if the number of conditions to check increases, I can then avoid having to write numerous IF conditions, which will also make the code very cumbersome.
I tried using List.Contains, but that does nt work, since here the partial string first needs to be extracted from the full string.
Any help appreciated
best regds.,
Table.AddColumn(#"Rounded Off", "Active", each {"Not Relevant","Aspertame","Sucralose","Stevia","Half-Sugar"}{List.PositionOf({{"GOLD","GLD"},{"NATURA"},{"GREEN","GRN"},{"SUGARLITE"}},[MATERIAL DESC],0,(x,y)=>List.Contains(x,y,(x,y)=>Text.Contains(y,x)))+1)Hi monojchakrab ,
Missing a ")" in the syntax (after x[Case]).
Table.AddColumn(#"Added Custom2", "Custom", each try Table.SelectRows(LookupTable, (x)=> Text.Contains([MATERIAL DESC],x[Case]))[Return]{0}) otherwise "Not Relevant")
10 Replies
Table.AddColumn(#"Rounded Off", "Active", each {"Not Relevant","Aspertame","Sucralose","Stevia","Half-Sugar"}{List.PositionOf({{"GOLD","GLD"},{"NATURA"},{"GREEN","GRN"},{"SUGARLITE"}},[MATERIAL DESC],0,(x,y)=>List.Contains(x,y,(x,y)=>Text.Contains(y,x)))+1)Hi wdx223_Daniel - that one worked like a charm.
Would you mind stepping the logic out for me so that I could understand how exactly is the code working...that could help as a future reference for similiar situation in other files.
Hi monojchakrab ,
You can create a separate table as below:
TableName - Lookup
In the main query:
Replace the formula below with your existing custom column:
try Table.SelectRows(Lookup, (x)=> x[Case]=[Text])[Return]{0} otherwise "Not Relevant"The code looks up the lookup table, returning the value if found else "not relevant"
Regards
KT
Thanks KT_Bsmart2gethe for picking this up...
I will definitely try this out.
Question : where in the M code should I supply the original column name, say, [Material Desc.], which has the text string on which this partial matches (e.g., Gold, gld, green, grn...etc) need to be performed? should I be writing this code as a new step by adding a new column in the main query or may be even replace the code in the added column, wherein I wrote the original code?
Thanks
Hi monojchakrab ,
You don't need to add a need step as this is a replacement step to your existing code (i.e. the custom column).
I have added [MATERIAL DESC] and Text.Contains() to the code below:
try Table.SelectRows(Lookup, (x)=> Text.Contains([MATERIAL DESC],x[Case])[Return]{0} otherwise "Not Relevant"Regards
KT
Hey KT_Bsmart2gethe , I tried this code as follows in a new custom column :
= Table.AddColumn(#"Added Custom2", "Custom", each try Table.SelectRows(LookupTable, (x)=> Text.Contains([MATERIAL DESC],x[Case])[Return]{0}) otherwise "Not Relevant")But this is returning tables in the custom column as below :
And the pop-up window when I am trying to expand the column..
Am I getting something wrong?
Appreciate your working with me on this
Thanks and best regds.,
