M Query Guide

Transformations

M Pivoting Columns

Pivoting Table Columns

M pivoting columns uses Table.Pivot for reshaping data.

Introduction to Pivoting Columns

Pivoting columns in M allows you to transform data by turning unique values from one column into multiple columns in a new table. This is achieved using the Table.Pivot function, which is part of M's rich set of data transformation capabilities. Pivoting is especially useful when you want to restructure data for better readability or further analysis.

Understanding Table.Pivot Function

The Table.Pivot function in M is used to reshape a table by pivoting columns based on unique values from a specified column. The syntax for Table.Pivot is:

Table.Pivot(table as table, pivotColumn as text, valueColumn as list, aggregationFunction as function) as table

The function takes four parameters:

  • table: The input table to be pivoted.
  • pivotColumn: The column whose unique values will become new column headers.
  • valueColumn: The column containing values to aggregate.
  • aggregationFunction: The function to aggregate the data, such as List.Sum.

Basic Example of Using Table.Pivot

Consider a table of sales data with columns for Product, Region, and Sales. We want to pivot this data to see total sales for each product across different regions.

In this example, the Region column is used to create new columns, and sales amounts are summed up across these regions for each product. The result is a table where regions are pivoted into columns, showing total sales for each product.

Advanced Usage and Tips

When working with Table.Pivot, consider the following tips:

  • Ensure that the pivotColumn contains the unique values you want as headers.
  • If the valueColumn has non-numeric data, choose an appropriate aggregation function, such as List.First or List.Count.
  • Handle errors with Try and Otherwise to manage unexpected values or missing data.

By mastering Table.Pivot, you can effectively reshape your data for improved analysis and reporting.

Conclusion

Pivoting columns with Table.Pivot in M is a powerful technique for transforming and analyzing data in new ways. Whether you're preparing data for reports or simply reorganizing it for clarity, understanding how to use this function is an essential skill for data analysts and developers working with M.

Transformations

Next
Unpivoting Columns