Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
monojchakrab
Resolver III
Resolver III

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.,

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

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)

View solution in original post

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_1-1661769887395.png

 

 

View solution in original post

10 REPLIES 10
wdx223_Daniel
Super User
Super User

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.

monojchakrab
Resolver III
Resolver III

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 :

monojchakrab_0-1661760141862.png

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.,

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_1-1661769887395.png

 

 

whoops @KT_Bsmart2gethe ....it has worked this time. This is an elegant solution

Hi @monojchakrab ,

 

Would you be able to screenshot the lookup table, including the name? Also, screenshot the preview of each sub-table, and if possible, the sample of column - [MATERIALS DESC].

 

Regards

KT

Here goes...

monojchakrab_0-1661766350072.png

The [Material Desc] column :

 

monojchakrab_1-1661766419988.png

I have added number steps to the query - is there any specific step, from which you require the sub-table?

KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi @monojchakrab ,

 

You can create a separate table as below:

 

TableName - Lookup

KT_Bsmart2gethe_0-1661645401514.png

 

 

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"

KT_Bsmart2gethe_1-1661645462542.png

 

Regards

KT

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.