M Query Guide

Transformations

M Grouping Rows

Grouping Table Rows

M grouping rows uses Table.Group for aggregations.

Introduction to Grouping Rows in M

In Power Query M language, Table.Group is a function used to group rows based on one or more columns, allowing for powerful data aggregation. This operation is essential when working with large datasets where summarizing data by categories is needed. Grouping rows can help you perform calculations like sum, average, count, and more on grouped data.

Basic Syntax of Table.Group

The basic syntax of the Table.Group function is as follows:

Here, table is the input table you want to group, key is a list of columns to group by, and operations is a list of operations to perform on the grouped data.

Example: Grouping by a Single Column

Consider a table named SalesData with columns Product, Month, and Sales. To group this data by the Product column and sum up the Sales, use the following code:

In this example, the Table.Group function groups rows by Product and calculates the total sales for each product. The result is a new table with two columns: Product and TotalSales.

Advanced Example: Grouping by Multiple Columns

To perform a more complex grouping operation, you can group by multiple columns. Let's extend our previous example to group by both Product and Month, and calculate the total sales:

In this advanced example, the data is grouped by both Product and Month, allowing you to see total sales for each product per month.

Common Aggregation Functions

When using Table.Group, you can apply various aggregation functions, such as:

  • List.Sum: Calculates the sum of values.
  • List.Average: Calculates the average of values.
  • List.Count: Counts the number of items.
  • List.Max: Finds the maximum value.
  • List.Min: Finds the minimum value.

These functions can be used in the operations list to perform different types of aggregations on your data.

Conclusion

Using Table.Group in M language is a powerful way to aggregate and summarize data in Power Query. By grouping rows and applying various aggregation functions, you can efficiently analyze and transform large datasets. Understanding how to use this function effectively will greatly enhance your data manipulation capabilities in Power Query.

Transformations

Next
Pivoting Columns