Table.Pivot

D

Back to top

T

Back to top

#

Back to top

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

Pivoting is the process of converting rows of data into columns. This process is useful when you want to summarize data in a more meaningful way. The Table.Pivot M function in Power Query helps to simplify this process.

Understanding The Table.Pivot Function

The Table.Pivot function transforms a table into a new table by pivoting the values from a specified column into new columns. The function allows you to specify the aggregation function to apply to the values that are pivoted.

Syntax:

Table.Pivot(Table as table, pivotColumn as text, valueColumn as text, [aggregationFunction], [additionalAggregationFunctions], [groupByColumns])

Parameters

– Table – The input table to be pivoted.

– pivotColumn – The name of the column whose values will be pivoted.

– valueColumn – The name of the column that contains the values to be aggregated.

– aggregationFunction – (optional) The aggregation function to apply to the values being pivoted. The default aggregation function is “sum”.

– additionalAggregationFunctions – (optional) The additional aggregation functions to apply to the values being pivoted.

– groupByColumns – (optional) The columns to group by.

Examples

#### Example 1: Basic Usage

Suppose we have a table of sales data that looks like this:

| Product | Year | Quarter | Sales |

|———|——|———|——-|

| A | 2020 | Q1 | 100 |

| A | 2020 | Q2 | 200 |

| B | 2020 | Q1 | 50 |

| B | 2020 | Q2 | 150 |

We can use the Table.Pivot function to pivot the sales data by quarter, as follows:

let

Source = ,

#"Pivoted Column" = Table.Pivot(Source, "Quarter", "Sales", "sum")

in

#"Pivoted Column"

The resulting table will look like this:

| Product | Year | Q1 | Q2 |

|———|——|——-|——-|

| A | 2020 | 100 | 200 |

| B | 2020 | 50 | 150 |

#### Example 2: Multiple Aggregation Functions

We can also apply multiple aggregation functions to the values being pivoted. Suppose we want to pivot the sales data by product and year, and calculate both the sum and count of sales for each combination:

let

Source = ,

#"Pivoted Column" = Table.Pivot(Source, "Year", "Sales", {"sum", "count"}, null, {"Product"})

in

#"Pivoted Column"

The resulting table will look like this:

| Product | Year | Sales_sum | Sales_count |

|———|——|———-|————-|

| A | 2020 | 300 | 2 |

| B | 2020 | 200 | 2 |

The Table.Pivot function in Power Query is a powerful tool that simplifies the process of pivoting data. It allows you to easily transform a table by pivoting the values of a specified column into new columns. With the ability to apply aggregation functions to the pivoted values, you can summarize your data in a more meaningful way.

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)