Power BI: Pivoting and Unpivoting Data

Before visualizing or modeling data, analysts often need to reshape tables into the right structure for reporting.
Power BI provides two key tools in Power Query Editor to do this:

• Pivot Columns — converts rows into columns, summarizing values (useful for cross-tab reports).

• Unpivot Columns — converts columns into rows, ideal for normalizing wide tables into long, tidy formats for DAX and visuals.

In this exercise, you’ll use the powerbi_ui_example.xlsx dataset to practice both transformations using the Revenue_Country and Sales tables.

Part 1 – Pivot Columns: Summarizing Revenue by Region

Objective

To transform the Revenue_Country table from a long format (rows by country) into a wide format showing Revenue by Region as separate columns.

Steps

Step 1: Load the Dataset

1. Open Power BI Desktop.

2. Go to Home → Get Data → Excel.

3. Select powerbi_ui_example.xlsx.

4. Check the Revenue_Country table.

5. Click Transform Data (not Load) to open Power Query Editor.

Step 2: Review the Source Table

The table currently looks like this:

Each row represents a country with its total revenue.
You’ll pivot this so that each Region becomes a column.

Step 3: Pivot the Column

1. Select the column Region.

2. On the Transform tab → click Pivot Column.

3. In the dialog box:

   • Values Column: choose RevenueTotal.

   • Aggregation Function: select Sum.

4. Click OK.

Step 4: Review the Result

You now have one row per Country, and separate columns for each Region:

This format is useful for cross-tab visualizations like matrix tables.

Step 5: Load to Power BI

1. Click Close & Apply to load the transformed table.

2. Create a Matrix Visual to verify the pivot:

   • Rows → Country

   • Columns → Region

   • Values → Sum of RevenueTotal.

Part 2 – Unpivot Columns: Normalizing Product Revenue Data

Objective

To flatten a pivoted dataset back into a row-wise format suitable for DAX and visualizations.

You’ll use the Sales table to simulate this. Suppose you’ve created an Excel summary like this:

This table is wide and cannot easily be used for visuals.
We’ll unpivot it back into a tidy format.

Steps

Step 1: Load the Table

1. In Power BI Desktop, open Transform Data again.

2. In the left panel, select your Sales table (or import a small “Quarterly Summary” version if you have one).

Step 2: Select Columns to Unpivot

1. Highlight all columns except ProductID (i.e., Q1, Q2, Q3, Q4).

2. Go to Transform → Unpivot Columns.

Power BI will convert the selected columns into two new fields:

• Attribute — stores the original column name (e.g., Q1, Q2, etc.).

• Value — stores the numeric value from those columns.

Step 3: Rename Columns

Rename the generated fields:

• Attribute → Quarter

• Value → Revenue

You now have this normalized table:

Step 4: Load and Visualize

1. Click Close & Apply to load the unpivoted data.

2. Create a Line Chart:

   • X-Axis → Quarter

   • Y-Axis → Revenue

   • Legend → ProductID

This displays revenue trend lines per product — something impossible with the original wide table.

Summary of What You Learned

Business Context

• Pivoting is useful when preparing cross-tab summaries for reports, such as comparing regions, quarters, or product categories side-by-side.

• Unpivoting is essential before loading data into the Power BI data model, ensuring your dataset follows a long, tidy format—the preferred structure for DAX calculations, filters, and time-series visuals.