Table.AggregateTableColumn

D

Back to top

T

Back to top

#

Back to top

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

What is Table.AggregateTableColumn?

Table.AggregateTableColumn is an M function in Power Query that allows users to aggregate data in a table based on a specific column. This function takes three arguments: the table to aggregate, the column to group by, and the aggregation function to apply. The aggregation function can be any of the built-in functions in Power Query, such as Sum, Average, Min, Max, or Count.

The M Code Behind Table.AggregateTableColumn

The M code behind Table.AggregateTableColumn is relatively simple. The function takes three arguments: the table to aggregate, the column to group by, and the aggregation function to apply. The M code for this function is as follows:

(Table as table, ColumnToGroupBy as text, AggregationFunction as function) =>

Table.Group(Table, {ColumnToGroupBy}, {{"Aggregation", AggregationFunction, ColumnToAggregate}})

Let’s break down this code line by line:

– `(Table as table, ColumnToGroupBy as text, AggregationFunction as function) =>`: This line defines the function parameters, which are the table to aggregate, the column to group by, and the aggregation function to apply.

– `Table.Group(Table, {ColumnToGroupBy}, {{“Aggregation”, AggregationFunction, ColumnToAggregate}})`: This line groups the table by the specified column and applies the specified aggregation function. The result is a new table with the aggregated values.

Using Table.AggregateTableColumn in Power Query

Table.AggregateTableColumn can be used in Power Query to transform and analyze data in a variety of ways. Here are some examples of how this function can be used:

Example 1: Summing a Column by Group

Suppose we have a table with sales data for different products and we want to sum the sales by product category. We can use Table.AggregateTableColumn to aggregate the sales data by category and sum the values. Here’s the M code to do this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ11Y0sS0pNKkgsSQQaA0J5gXlgNOgHcnJyfRz8vPz8ksjQ1MkxKLcnIL0oB+wzUgC0KzQ0yUipQKM8M0c2GwB", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Product = _t, Sales = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Product", type text}, {"Sales", Int64.Type}}),

#"Grouped Rows" = Table.AggregateTableColumn(#"Changed Type", "Category", "Sales", "Sum")

in

#"Grouped Rows"

This code imports the sales data from a JSON file, transforms the data types, groups the data by category, and applies the Sum function to the Sales column.

Example 2: Finding the Maximum Value by Group

Suppose we have a table with customer ratings for different products and we want to find the highest rated product in each category. We can use Table.AggregateTableColumn to group the data by category and find the maximum rating value. Here’s the M code to do this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ11Y0uyklNLy0u0jNwN0cxT0jVrCgKAKJxkAI=", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Product = _t, Rating = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Product", type text}, {"Rating", Int64.Type}}),

#"Grouped Rows" = Table.AggregateTableColumn(#"Changed Type", "Category", "Rating", "Max")

in

#"Grouped Rows"

This code imports the customer rating data from a JSON file, transforms the data types, groups the data by category, and applies the Max function to the Rating column.

Table.AggregateTableColumn is a powerful M function in Power Query that allows users to aggregate data in a table based on a specific column. This function can be used in a variety of ways to transform and analyze data. By understanding the M code behind this function, users can gain a deeper understanding of how Power Query works and how to use it to solve data problems.

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)