[Power Query] How to Get the Minimum Value of a Column and Display it in a Custom Column

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 column column1 from 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.