Examples
M Pivot Query
Pivoting Sales Data
M pivot query reshapes sales data by month with Table.Pivot.
Understanding M Pivot Queries
The M Pivot Query is a powerful tool in Power Query that allows you to reshape your data. Specifically, it can transform rows into columns, enabling you to summarize and display data in a more intuitive format. This is particularly useful for monthly sales data, where you might want to view sales figures for each month side-by-side.
Basic Structure of Table.Pivot
The Table.Pivot function in M language is used to convert row values into columns. The basic syntax is as follows:
Example: Pivoting Sales Data by Month
Let's say you have a sales dataset that lists sales transactions by date, product, and amount. You want to pivot this data to show total sales per product for each month. Here's how you can do it:
In this example, Excel.CurrentWorkbook(){[Name="SalesData"]}[Content] loads the sales data from an Excel table named SalesData. The Table.Pivot function then pivots this data, using the Month column as the pivot column and aggregating sales amounts using List.Sum.
Handling Missing Values
When pivoting data, you may encounter missing values. By default, Table.Pivot will create null entries for missing data. You can handle these by using the Record.FieldOrDefault function to replace nulls with a default value, such as 0.
This code snippet demonstrates how to replace null values with zeros after pivoting.
Examples
- Previous
- Aggregation Query
- Next
- Join Query
