Find Maximum Value in a Column in Power Query

Sometimes, you may want to find the maximum value in a column in Power Query. For example, it could be helpful to highlight the highest sales transaction in a dataset for reporting.

I will show you how to find the maximum value in a column in Power Query using the Power Query Editor’s Graphical User Interface (GUI) and the Power Query Formula Language (M).

Method #1: Using the Power Query Interface to Find the Max Value in a Column

Suppose you have the Excel table below showing the annual sales figures for several salespeople.

Data set to highlight the maximum value in power query.

You want to find the maximum sales figure in the AnnualSales column in Power Query.

Here’s how to do it using the Power Query Editor’s GUI:

  1. Select any cell in the table.
  2. Open the Data tab and click the From Table/Range command button on the Get & Transform Data group.
Click on the front table or range icon in the data tab in the ribbon in Excel.

The above step loads the table data into the Power Query Editor.

The data is loaded in Power Query Editor.
  1. Select the AnnualSales column by clicking its header.
  2. Click the Transform tab, open the Statistics drop-down on the Number Column group, and click the Maximum option.
Click on the maximum option available in the statistics drop-down public query editor.

The above step reduces the entire table to a single value, the maximum value in the AnnualSales column.

It gives you the maximum value as a new step. In Power Query.
  1. Load the query data back into Excel.

Method #2: Using Power Query Formula Language (M) to Find the Max Value in a Column

Let’s say you have the Excel table below showing the annual sales figures for several salespeople.

Data to highlight the maximum value in a column in Power Query Editor.

You want to find the maximum sales figure in the AnnualSales column in Power Query.

Here’s how to do it using the Power Query Formula Language:

Step #1: Load the Table Data into the Power Query Editor

Below are the steps to load the Excel table into Power Query Editor:

  1. Select any cell in the table.
  2. Open the Data tab and click the From Table/Range command button on the Get & Transform Data group.

Step #2: Add a Custom Column to the Query Table

Use the steps below to add a custom column with a custom formula to the query table: 

  1. On the Power Query Editor, open the Add Column tab and click the Custom Column command button.
Click on the custom column option in the add column tab.

The above step opens the Custom Column dialog box.

  1. Do the following on the Custom Column dialog box:
  • Enter the text ‘MaxSales’ in the ‘New Column name’ box.
  • Place the cursor after the equal sign on the ‘Custom column formula’ box and enter the following formula: 
=List.Max(#"Changed Type"[AnnualSales])

Your Custom Column dialog box should look like the one below.

Enter the list.max formula in the custom column dialog box.
  1. Click OK.

The above step adds a custom MaxSales column to the table, populated with the maximum value from the AnnualSales column.

A new column is added that shows the maximum sales in the entire column.
  1. Load the query data back into Excel.

While just adding the maximum sales value column may not be very useful, you can use it for more meaningful analysis, such as finding the percentage of sales by each salesperson compared to the maximum sales.

Explanation of the Formula

= List.Max(#”Changed Type”[AnnualSales])

The formula returns the highest annual sales figure from the AnnualSales column in the table generated in the #”Changed Type” step.

Here’s how it does it:

  • #”Changed Type” – In this example, this refers to the most recent step in the Power Query Editor. In your case, the step name might be different, so be sure to adjust the formula to match your specific query.
  • [AnnualSales] – This part extracts a list of all values from the AnnualSales column.
  • List.Max(…) – The function takes the list of values extracted in the previous step and returns the maximum value from the list. 

I have shown you how to find the maximum value in a column in Power Query using the Power Query Editor GUI and the Power Query Formula Language (M). I hope you found the tutorial helpful.

Other Excel and Power Query articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.