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