Table.Group

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.Group

In this article, we will explore the M code behind the Table.Group function and how it can be used to transform data.

What is the Table.Group function?

The Table.Group function is used to group rows of data based on one or more columns. It takes an input table and a list of grouping columns as arguments. The output is a table with one row for each unique combination of grouping column values.

For example, let’s say we have a table of sales data with columns for Region, Product, and Sales Amount. We want to know the total sales by region and product. We can use the Table.Group function to group the data by Region and Product and sum the Sales Amount column.

Syntax of the Table.Group function

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

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number) as table

– `table`: The input table to group

– `key`: A list of column names or expressions to group by

– `aggregatedColumns`: A list of columns to aggregate

– `groupKind`: An optional parameter to specify the grouping algorithm. The default is 0, which uses hash-based grouping.

Examples of using the Table.Group function

Let’s look at some examples of using the Table.Group function.

Example 1: Grouping by a single column

Suppose we have a table of sales data with columns for Region, Product, and Sales Amount. We want to group the data by Region and find the total sales for each region.

The M code for this would be:

Table.Group(

salesData,

{"Region"},

{{"Total Sales", each List.Sum([Sales Amount]), type number}}

)

– `salesData`: The input table

– `{“Region”}`: A list containing the name of the column to group by

– `{ {“Total Sales”, each List.Sum([Sales Amount]), type number} }`: A list containing the column to aggregate, the aggregation function (List.Sum), and the data type of the result.

Example 2: Grouping by multiple columns

Let’s say we want to group the sales data by Region and Product and find the total sales for each combination.

The M code for this would be:

Table.Group(

salesData,

{"Region", "Product"},

{{"Total Sales", each List.Sum([Sales Amount]), type number}}

)

– `{“Region”, “Product”}`: A list containing the names of the columns to group by

– `{ {“Total Sales”, each List.Sum([Sales Amount]), type number} }`: A list containing the column to aggregate, the aggregation function (List.Sum), and the data type of the result.

Example 3: Using custom aggregation functions

The Table.Group function allows us to use custom aggregation functions. Let’s say we want to group the sales data by Region and find the minimum and maximum sales for each region.

The M code for this would be:

Table.Group(

salesData,

{"Region"},

{

{"Min Sales", each List.Min([Sales Amount]), type number},

{"Max Sales", each List.Max([Sales Amount]), type number}

}

)

– `{“Region”}`: A list containing the name of the column to group by

– `{ {“Min Sales”, each List.Min([Sales Amount]), type number}, {“Max Sales”, each List.Max([Sales Amount]), type number} }`: A list containing the columns to aggregate, the aggregation functions (List.Min and List.Max), and the data types of the results.

The Table.Group function is a powerful tool for grouping and aggregating data in Power Query. It allows users to easily summarize data by one or more columns, and even allows for custom aggregation functions. Understanding the M code behind this function can help users master the art of data transformation in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)