When you land into the Power Query Editor for Excel or Power BI, I wish we have a built-in feature to filter the maximum or minimum value.
If you like this idea and wish to make happen, please vote on the Excel User Voice <<Click Here>>
However, we can achieve the same by slightly changing our M code. There is another way to handle this situation, but I find editing the M code is easy and saves some steps.
Let us look at the example for making the max value dynamic then extracting the min value will be similar.
In this sample data set each time we need to extract the highest sales value (max value) from the data set and this value should be dynamic whenever the new highest value is added to the records.
Load data into the Power Query Editor for Excel or Power BI.
Filter the column value (Sales Amount) with any number.
⚠️ Note: The data type shouldn’t be text data type.
We need this below formula as highlight in the red box automatically generated by the Power Query
= Table.SelectRows(Source, each ( [Sales Amount] = 219) )
Replace the number 219 with the below formula.
List.Max( Source[Sales Amount] )
We are referencing to the previous step i.e. Source and selecting the column [Sales Amount] for the Source. It will convert to list and we are wrapping up that into List.Max to get the highest value. Using that max value we will filter our data set in the current step.
Now the formula should look
= Table.SelectRows(Source, each ([Sales Amount] = List.Max( Source[Sales Amount] ) ) )
All done, we have our dynamic maximum value (highest values sales).
Likewise to get the minimum value instead of using the function List.Max we will use the function List.Min that will extract the minimum value (Lowest value Sales).
The steps for minimum value remain the same and the formula should read as below
= Table.SelectRows(Source, each ([Sales Amount] = List.Min( Source[Sales Amount] ) ) )
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.