Tag Archives: M Query

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

ByHariharan Rajendran

Min and Max value in Power Query Power BI

Let me ask a simple question.

How to get the minimum value in Power BI? Everyone answers min() which will give minimum value.

This can be achieved in both Power Query and DAX layers.

As you know MIN(<columnname>) measure will be filtered down based on the dimension column that we use. In this post, I am explaining how to achieve the MIN / MAX value in Power Query.

Scenario – I have a below table and I want to show the minimum amount value of A & B also I want to show the original amount.

Current Data

Expected Result

A’s minimum value is 12 and B’s minimum value is 5. I want to show these numbers on A & B as below.

Let me walk through the steps to achieve an expected result.

Step 1:  use Group by ID transformation and choose “All Rows” as operation as below.

Step 2 – Create a new column with the below expression.

Table.Column([Count],”Amount”)

Here “Amount” is the column that was available before Group by. Need to provide that column name on Tabe.Column function and Count is nothing but the new column which we got as a result of the group by.

Step 3 – Either create a new calculated column or update the previous column with an additional expression as below. That expression List.Sort. The result of the step is a list. We need to sort the list in ascending order.

List.Sort(Table.Column([Count],”Amount”))

Step 4: As we are targeting for minimum value, we need to get the first value from the sorted list. Use the List.First function.

List.First(List.Sort(Table.Column([Count],”Amount”)))

The final expression will be like below.

Step 5 – Now, we will get the result as below. Remove the Count column and we will be getting only ID & Custom columns.

Step 6: Name this table and go to the main table and use merge transformation and merge with the new table using the ID column.

Step 7 – Expand the column and we will get the below-expected result.

If you want to get the maximum then use descending on the list.sort then the rest of the steps will be the same.

Happy Learning.

1