In one of my blogs post we saw filtering data by slightly adding a touch of M code, but today we will see how to filter the min and max value with the UI method without using the M code.
Note: With this technique we can only select one row from the record, if you have ties and need to get both the records then you need to refer to this blog <coming soon>.
Below is our data set
Filtering the Max Value – NO TIES
- Load data into the Power Query Editor for Excel or Power BI.
- Change Data Type to Number > (Decimal, Whole Number, Currency) depending on the data set.
⚠️ Note : The data type shouldn’t be text data type.
- Sort the data in the descending order
- Go to Home >Keep Top Rows > 1
By this we will get our max value without Ties.
Similar way we can get the min value, where you just need to sort the data in ascending order and repeated the above step.
⚠️ Sorting of data is a very important step:
When you sort the data in descending (Largest to Smallest) then you shall Keep Top Rows 1 for max value and Keep Bottom Rows for min value.
When you sort the data in ascending (Smallest to Largest) then you shall Keep the Top Rows 1 for the min and Keep Bottom Rows for max value.
You can even add multiple level of sorting of data and then select the rows accordingly.
Download the sample file for Excel & Power BI << CLICK HERE >>
Was the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
If no, please let us know what to improve.
Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.