Mastering M Language: Grouping and Aggregating Data in Power Query

Image by Editor
 

Grouping and aggregating data are fundamental operations in data analysis. Power Query’s M Language helps to group, aggregate, and summarize large datasets. One of its most powerful tools is the Table.Group function, which lets you group by one or more columns and perform various types of aggregations.

In this tutorial, we will show how to master the M language for grouping and aggregating data in Power Query.

What is Table. Group in M Language?

The Table .Group function is the primary method for performing grouping and aggregation in M Language. This function groups rows based on one or more columns and allows custom aggregations on other columns. It can perform multiple aggregations simultaneously. Handles complex logic that cannot be achieved using the user interface alone.

Syntax:

Table.Group(table as table, keyColumns as list, aggregatedColumns as list) as table

Parameters:

• table: The input table.
• keyColumns: List of column names to group by.
• aggregatedColumns: List of aggregation definitions, including column names, aggregation logic, and output data types.

How to Insert and Use M Language Code

Step 1: Load Data into Power Query

Excel:

• Select the cell range.
• Go to the Data tab >> select From Table/Range.

Power BI: 

• Load the Data into Power BI.
• Go to Home tab >> select Transform Data.

Step 2: Insert M Language Code

Insert Blank Query:

• Go to the Home tab >> click New Sources >> click Other Sources >> select Blank Query.
• Give a proper query name based on the operation.

Open Advanced Editor:

• Select your query.
• Go to the Home tab >> select Advanced Editor.
• Insert the M language code to perform grouping and aggregations.

1. Group by Single Column

Let’s use M language to group by Region and aggregate(Sum) by Sales Amount to demonstrate grouping and aggregation.

M Language Code:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    
    // Group by Region and calculate total sales
    GroupedSales = Table.Group(Source, {"Region"}, 
    
    {
        // Aggregate total sales for each region
        {"Total Sales", each List.Sum([Sales Amount]), type number}
    })
in
    GroupedSales

2. Group by Multiple Columns

You can use grouping and aggregations for multiple columns. Let’s group by Product and Region and aggregate(Sum) Sales.

M Language Code:

3. Multiple Aggregations

You can perform multiple aggregations at a time using the Table.Group function.

Let’s group by Region and aggregate by:

• Sum: Total Sales
• Average: Average Sales
• Max: Max Sales
• Sum: Total Units
• Count: Transactions

M Language Code:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    
    // Group by Product Name
    GroupedSales = Table.Group(Source, {"Product Name"}, 

    {
        // Multiple aggregations
        {"Total Sales", each List.Sum([Sales Amount]), type number},
        {"Average Sales", each List.Average([Sales Amount]), type number},
        {"Max Sales", each List.Max([Sales Amount]), type number},
        {"Total Units", each List.Sum([Units Sold]), type number},
        {"Transactions", each Table.RowCount(_), Int64.Type}
        
    })
in
    GroupedSales

4. Add Derived Column and Group

Let’s summarize monthly sales by using other functions along with the Table.Group function.

• First, we will derive the Month from the Date.
• Then we will group by Month and aggregate by Sales Amount and Units Sold.

M Language Code:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],

    // Dervived Month Column
    AddMonth = Table.AddColumn(Source, "Month", each Date.MonthName([Date])),
    
    // Group by Product Name
    GroupedSales = Table.Group(AddMonth, {"Month"}, 

    {
        // Multiple aggregations
        {"Total Sales", each List.Sum([Sales Amount]), type number},
        {"Total Units", each List.Sum([Units Sold]), type number}
        
    })
in
    GroupedSales

Advanced Aggregation Techniques

1. Custom Record Aggregation (Max Sale Record)

In many business scenarios, you don’t just want the maximum value (like max sales amount), but also the associated context, such as the date, region, or units sold when that top sale occurred.

Let’s group data by Product and, for each product, return the entire row (record) that had the highest Sales.

M Language Code:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    
    // Group by Product Name
    Grouped = Table.Group(Source, {"Product Name"}, 
    
    {
        // Aggregate top sale with record
        {"Top Sale", each Table.Max(_, "Sales Amount"), type record}
    }),
    
    Expanded = Table.ExpandRecordColumn(Grouped, "Top Sale", {"Date", "Region", "Units Sold", "Unit Cost", "Sales Amount"})
in
	Expanded

2. Custom Aggregation Functions

You can create custom aggregation functions using M Language’s list manipulation capabilities. The custom variance function measures the consistency (or volatility) of sales.

M Language Code:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],

    // Custom function to calculate sales variance
    SalesVariance = (salesList) => 
        let
            avg = List.Average(salesList),
            squaredDiff = List.Transform(salesList, each Number.Power(_ - avg, 2)),
            variance = List.Average(squaredDiff)
        in
            variance,
    
    // Group by Region and calculate total sales
    GroupedSales = Table.Group(Source, {"Region"}, 
    {
        // Aggregate total sales for each region
        {"Sales Variance", each SalesVariance([Sales Amount]), type number}
    })
in
    GroupedSales

Based on the results, the West region shows the highest sales variance, suggesting more variability in sales amounts, while the East region has the lowest variance, suggesting more consistent sales figures.

Tips for Grouping in M Language

• Always define the result type (e.g., type number, Int64.Type, type record) to avoid load errors.
• You can chain Table.Group with functions like Table.Sort, Table.AddColumn, and Table.TransformColumns for advanced scenarios.
• Table.RowCount(_) is useful for counting transactions within a group.
• Use Table.Max, Table.Min, or List.Max for top-N analysis.

Conclusion

Mastering Table.Group and M Language aggregation techniques will open up powerful data summarization capabilities in Power Query. You can transform raw data into high-level summaries tailored to your analysis needs. M language gives you the flexibility and precision required for powerful reporting and dashboarding. Practice these techniques to efficiently transform and analyze your data.