M Query Guide

Examples

M Aggregation Query

Aggregating Sales Data

M aggregation query groups sales by region with sums.

Introduction to M Aggregation Queries

M Aggregation Queries are a powerful feature in Microsoft's Power Query M language, primarily used for data transformation and preparation. These queries allow you to group data and perform aggregate functions such as sum, average, minimum, and maximum. In this guide, we'll focus on how to group sales data by region and calculate the sum of sales using M Aggregation Query.

Understanding the Basic Structure

The basic structure of an M Aggregation Query involves using the Group By operation. This operation allows you to specify the columns to group by and the aggregation functions to apply. Here's the syntax:

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

Where:

  • table: The input table to perform the operation on.
  • key: A list of columns to group by.
  • aggregatedColumns: A list specifying the aggregation columns and functions.

Step-by-Step Example: Grouping Sales by Region

Let's walk through a step-by-step example where we use an M Aggregation Query to group sales data by region and calculate the total sales for each region. Consider the following sales data:

To group this data by region and sum the sales, you can use the following M code:

This code performs the following steps:

  • Defines the initial sales data as a table.
  • Uses the Table.Group function to group the data by the Region column.
  • Applies the List.Sum function to aggregate sales within each region.

The result is a new table with each region and the total sales for that region:

Region | Total Sales
North  | 400
South  | 300
East   | 150
West   | 250

Conclusion

The M Aggregation Query is a useful tool for summarizing data, especially when dealing with large datasets. By grouping sales by region and summing the sales, businesses can quickly identify which regions are performing well. Mastering these queries can significantly enhance your data transformation capabilities.

Examples

Next
Pivot Query