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.
This article talks about the pre-defined transformations that we can directly use in Power BI Desktop. These transformations are part of Power Query Editor.
As a business intelligence developer, usually we spend most of the time to prepare the dataset for report and dashboards. Preparing the dataset includes, getting the data from different sources, data modelling and transform the data.
Data transform is applicable for both rows and columns. Check the list of pre-defined transforms in Power BI Desktop.
These transforms are grouped into different categories.
To add a new column
To play with the columns,
To format a text column,
To format a number column,
Date and Time columns,
The above sections are specific to columns. To play with rows,
There any many situations where we need to combine the tables and queries. To handle those scenarios, we can use the below options in Power BI.
To sort the columns,
We also have R script which we can use to perform any complex transform or logics with the data.
Apart from the pre-defined options still we can do complex logics with M language (Power Query Language).
Happy Learning & Share your comments.