Introduction
While shaping and processing data in Excel’s Power Query, you may encounter situations where you want to retrieve the minimum value contained within a specific column.
In this article, I will share the procedure for calculating the minimum value of a column in Power Query and adding the result as a new custom column.
Goal
- Calculate the Minimum Value (Min) of a specific column for the table loaded in Power Query.
- Add that result as a new column (Custom Column).
Steps: Getting the Minimum Value in Power Query
Below, I will explain the method to add a custom column that calculates and displays the minimum value for a target column named column1.
1. Select “Custom Column” from the [Add Column] Tab
From the menu at the top of the Power Query Editor, click “Add Column” -> “Custom Column”.
The “Add Custom Column” window will open.
2. Configure the Custom Column
Enter a New Column Name
Enter any name you like in the “New column name” field. For this example, we will use:
column1_Min
Enter the Custom Column Formula
Enter the following M language code in the “Custom column formula” field.
"column1 Min: " & Text.From(List.Min(#"Changed Type"[column1]))
(Note: Replace #"Changed Type" with the actual name of the Previous Step in your Applied Steps list.)
Explanation of the Formula:
List.Min(...): Retrieves the minimum value from the specified list (column).#"Changed Type"[column1]: Specifies the columncolumn1from the table state in the previous step (e.g., “Changed Type”). Crucial: You must refer to the table name of the previous step to calculate the min value of the entire column.Text.From(...): Converts the retrieved numerical value into a text string."column1 Min: ": Adds a text label to the value.
3. Click [OK] to Add the Column
Once you have finished entering the formula, press [OK].
A new column displaying the minimum value should now be added to the right of the original column1.
Application: How to Get Max, Average, and Standard Deviation
M Language provides other useful aggregation functions.
By changing the List.Min() part of the formula, you can retrieve various statistics.
| Value to Retrieve | Function Replacement Example |
| Maximum | List.Max(...) |
| Average | List.Average(...) |
| Standard Deviation | List.StandardDeviation(...) |
Summary
In Power Query, you can easily retrieve the minimum value of a column using the List.Min() function.
Displaying this as a custom column can be useful for reports and as an aid for data visualization.
- Min:
List.Min() - Max:
List.Max() - Average:
List.Average() - Standard Deviation:
List.StandardDeviation()
I hope this article helps you utilize Power Query and understand M language better.
Thank you for reading to the end.
