M Query Guide

Transformations

M Unpivoting Columns

Unpivoting Table Columns

M unpivoting columns uses Table.Unpivot for normalization.

Introduction to Unpivoting Columns

Unpivoting columns is a crucial step in data normalization. In data transformation, unpivoting helps in converting wide data formats into long or tall formats. This is achieved through the Table.Unpivot function in M language, which is predominantly used in Power Query and Excel.

In this guide, we'll explore how to use the Table.Unpivot function to effectively transform your data.

Why Use Unpivoting?

Data often comes in a wide format with numerous columns that represent different categories or measures. While this format can be useful for reporting, it's not always ideal for analysis or data modeling. By unpivoting, you transform this data into a long format, which:

  • Enhances Data Analysis: Easier to apply aggregate functions and filters.
  • Improves Compatibility: Works better with most data modeling tools.
  • Facilitates Data Visualization: Simplifies the creation of charts and graphs.

Using Table.Unpivot Function

The Table.Unpivot function in M is used to convert columns into attribute-value pairs. This function takes three parameters:

  • table: The table to unpivot.
  • pivotColumns: The columns to unpivot.
  • attributeColumn: The name of the new column that will contain the names of the unpivoted columns.
  • valueColumn: The name of the new column that will contain the values of the unpivoted columns.

Example of Unpivoting Data

Let's consider a simple example to illustrate how unpivoting works. Suppose you have a table with sales data across different quarters:

In this example, the columns Q1, Q2, Q3, and Q4 are unpivoted into two columns: Quarter and Sales. The result is a longer table where each row represents a single quarter's sales for a product.

Benefits of Using Table.Unpivot

Using Table.Unpivot not only simplifies data processing but also enhances the flexibility of data analysis. By converting data into a long format, you can:

  • Apply filters and calculations more efficiently.
  • Create more dynamic visualizations.
  • Ensure consistency with data modeling practices.

Conclusion

Unpivoting columns using the Table.Unpivot function is an essential part of data transformation in Power Query. It enables you to convert wide datasets into a format that is better suited for analysis and visualization. Understanding how to apply this function will greatly enhance your data processing capabilities.

In the next post, we will explore Merging Tables to combine data from multiple sources.

Transformations

Next
Merging Tables