I have been writing this series of filtering the values in Power Query so in this post we will see how to handle the TIES in your data set.
Check these previous blog post
FILTER max or min value in Power Query for Excel & Power BI (Using M Code)
FILTER max or min value in Power Query using UI Method in Excel & Power BI – “NO TIES”
Below is our data set
Filtering the Max Value with 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 should not be text data type.
Select the Value Column i.e. [Sales Amount] > Go to Home > Group By
The important thing to note here in the group by “Sales Amount” value column should be in the grouping category and New Name Column Name as DATA (or whatever you like) > Operation > All Row
Select the Sales Amount column and sort the data in descending order to get the Max value and in ascending order to get the Min value. Depending on steps remain the same.
Go to Home >Keep Top Rows > 1
By this we will get our max value without Ties
Delete the Sales Amount Column > Expand DATA Column
We go the result in max values with no ties.
My personal preference is to use the M code as shown in my earlier blog post which eliminates the step, but there is no harm in using the UI Method.
⚠️ 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 the Bottom Rows for the max value.
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.