Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver III
4 years ago
Solved

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...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago
    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)
  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    4 years ago

    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")

     

     

     

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Impactful Individual
4 years ago

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