The Pivot transformation converts unique values in a column into multiple columns. This is useful when you want to aggregate data based on a specific category and display it in a more readable format.

Let's say you have a table with sales data that includes columns for "Date", "Product", and "Sales". If you want to see the total sales for each product by date, you can use the Pivot transformation to turn the "Product" column into multiple columns, one for each product.

let
 Source = Table.FromRecords({
 [Date = #date(2023, 1, 1), Product = "A", Sales = 100],
 [Date = #date(2023, 1, 1), Product = "B", Sales = 150],
 [Date = #date(2023, 1, 2), Product = "A", Sales = 120],
 [Date = #date(2023, 1, 2), Product = "B", Sales = 180]
 }),
 #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Product]), "Product", "Sales", List.Sum)
in
 #"Pivoted Column"

The Unpivot transformation, conversely, transforms columns into rows. This is helpful when you have multiple columns representing the same type of data and you want to consolidate them into a single column.

Imagine you have a table with sales data for different months, with each month represented as a separate column (e.g., "Jan", "Feb", "Mar"). To analyze the sales data across all months, you can use the Unpivot transformation to combine the month columns into a single "Month" column and create a corresponding "Sales" column.

let
 Source = Table.FromRecords({
 [Product = "A", Jan = 100, Feb = 120, Mar = 150],
 [Product = "B", Jan = 150, Feb = 180, Mar = 200]
 }),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value")
in
 #"Unpivoted Columns"

1. Open Power Query Editor: In Power BI Desktop, go to the "Home" tab and click "Transform Data".
2. Select Your Table: Choose the table you want to transform from the "Queries" pane.
3. Apply Pivot (if needed): Select the column you want to pivot, go to the "Transform" tab, and click "Pivot Column". Configure the pivot settings as needed.
4. Apply Unpivot (if needed): Select the columns you want to unpivot, go to the "Transform" tab, click the dropdown arrow under "Unpivot Columns", and choose the appropriate unpivot option.
5. Close & Apply: Once you've applied the transformations, click "Close & Apply" in the "Home" tab to load the transformed data into your Power BI model.

• Understand Your Data: Before applying Pivot or Unpivot, make sure you understand the structure of your data and the desired outcome.
• Choose the Right Transformation: Determine whether Pivot or Unpivot is the appropriate transformation for your specific scenario.
• Consider Performance: Large datasets can impact performance when using Pivot or Unpivot. Optimize your data model and transformations for better performance.

Pivot and Unpivot are powerful data transformation techniques in Power BI that allow you to reshape your data for better analysis and visualization. By understanding how these transformations work and following best practices, you can effectively prepare your data for meaningful insights.

```mdx
---
title: "Power BI: Pivoting and Unpivoting Your Data"
description: "Discover how to reshape your data within Power BI using the Pivot and Unpivot features located in Power Query Editor."
---


# Transforming Data Shapes: Pivot and Unpivot in Power BI


Data analysis heavily relies on the ability to transform data, and Power BI offers robust functionalities to reshape your data according to your analytical goals. Among these, Pivot and Unpivot are two particularly useful transformations. This guide will walk you through utilizing these tools within Power BI's Power Query Editor.


## Delving into Pivot


The Pivot transformation reorganizes unique entries from a selected column into distinct, new columns. This is particularly helpful when you aim to aggregate data based on a particular category and present it in a more easily digestible format.


### Pivot in Action: An Example


Consider a table containing sales information, featuring columns such as "Date", "Product", and "Sales Amount". If your objective is to view the total sales amount for each product on each date, you can employ the Pivot transformation to convert the "Product" column into individual columns, each representing a specific product.


```powerquery
let
 Source = Table.FromRecords({
 [Date = #date(2023, 1, 1), Product = "A", Sales = 100],
 [Date = #date(2023, 1, 1), Product = "B", Sales = 150],
 [Date = #date(2023, 1, 2), Product = "A", Sales = 120],
 [Date = #date(2023, 1, 2), Product = "B", Sales = 180]
 }),
 #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Product]), "Product", "Sales", List.Sum)
in
 #"Pivoted Column"

Conversely, the Unpivot transformation converts columns into rows. This proves beneficial when you have multiple columns representing a similar type of data and your aim is to combine them into a single, unified column.

Suppose you have a table that stores sales figures for different months, with each month represented by its own column (e.g., "January", "February", "March"). To effectively analyze sales trends across all months, you can leverage the Unpivot transformation to merge the month columns into a single "Month" column, along with a corresponding "Sales" column.

let
 Source = Table.FromRecords({
 [Product = "A", Jan = 100, Feb = 120, Mar = 150],
 [Product = "B", Jan = 150, Feb = 180, Mar = 200]
 }),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value")
in
 #"Unpivoted Columns"

1. Access Power Query Editor: Within Power BI Desktop, navigate to the "Home" ribbon and select "Transform Data".
2. Choose Your Table: From the "Queries" panel, select the specific table you intend to transform.
3. Implement Pivot (if applicable): Select the column you wish to pivot, proceed to the "Transform" ribbon, and click "Pivot Column". Configure the pivot settings according to your requirements.
4. Implement Unpivot (if applicable): Select the columns you wish to unpivot, navigate to the "Transform" ribbon, click the dropdown menu under "Unpivot Columns", and choose the appropriate unpivot option that suits your needs.
5. Apply and Finalize: After applying the transformations, select "Close & Apply" from the "Home" ribbon to integrate the transformed data into your Power BI model.

• Data Understanding is Key: Prior to using Pivot or Unpivot, ensure a thorough understanding of your data's structure and the desired outcome of the transformation.
• Appropriate Transformation Selection: Carefully determine whether Pivot or Unpivot is the more suitable transformation for your particular data scenario.
• Performance Considerations: Be aware that using Pivot or Unpivot on very large datasets can impact processing performance. Optimize your data model and transformations to achieve better efficiency.

Pivot and Unpivot are potent data transformation techniques available in Power BI, empowering you to reshape your data for enhanced analysis and visualization. By grasping the mechanics of these transformations and adhering to best practices, you can effectively prepare your data to unlock valuable insights.

<AppearanceSection></AppearanceSection>