Tag Archives: Power Query Tutorial

ByHariharan Rajendran

List.Contains M Query | Importance of M Query

Hi, Happy new year to everyone.

In this blog post, I am explaining the importance of learning M Query. Let me tell you the scenario.

Scenario:

I have a simple table as like below,

I need to create a new column based on the Category column. The logic is If Category is A then Yes otherwise No. This can be easily achieved by adding the if-else condition as below.

If [Category]=”A” then “Yes” else “No”.

In the same scenario, if I need to include A & B both then I need to modify the expression as below.

If [Category]=”A”  or [Category]=”B” then “Yes” else “No”.

Let us take the below table and I want to consider A,B,C,D,E,F. if any of these values then “Yes” otherwise No. In this case, if I use if and else then I  need to keep add or condition to cover up all the values. The final expression would be as below.

 

If [Category]=”A”  or [Category]=”B” or [Category]=”C”  or [Category]=”D” or [Category]=”E” or [Category]=”F”   then “Yes” else “No”.

Even sometimes, we may get more conditions in the above expression. The above expression keeps grow based on the number of values we want to analyze.

 

Solution – Simple and Easy

As I explained, this is logic almost every Power BI developer prefer or use on their Projects. If you know M Query or Power Query then you can simplify the logic. Let me explain, how to achieve the same result with simplified logic.

I am introducing one of the M Query function called “List.Contains”. This is one of the Powerful functions. Use this function to minimize the expression.

Syntax for List.Contains

List.Contains(list as list, value as any, optional equationCriteria as any) as logical

 

if(List.Contains({“A”,”B”,”C”,”D”,”E”,”F”},[Category])=true) then “Yes” else “No”

That’s it. It gives you the same result. If you don’t want to use the hardcoded value you can create a list with A to F values again you can get the main table and use that list on the expression as below.

The above T3 list used on the below expression.

Explained the same in video. Please check it.

Use this M function to simplify the expression. Please leave your comments below. Thanks

1