What is Pivoting and Unpivoting in Power BI?

If you've spent any time with Power BI, you've probably heard that the starting shape of your data is everything. Getting your data into the right format is the difference between a dashboard that works seamlessly and one that requires hours of complex DAX formulas. This is where pivoting and unpivoting come in - two incredibly powerful transformation tools that help you reshape your data for optimal analysis.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This tutorial will break down exactly what pivoting and unpivoting are in Power BI. We’ll cover why one is used far more often than the other, and walk through step-by-step examples of how to use both to make your reporting life easier.

Why Data Shape Matters in Power BI

Before touching the pivot or unpivot buttons, it's essential to understand the concepts of "wide" and "tall" data. How your data is structured fundamentally changes how Power BI can interact with it.

"Wide" Data: Easy for Humans, Tough for Power BI

Wide data is a format we see all the time in spreadsheets. It’s structured to be easily readable for people. Typically, each row represents a single item (like a product or a customer), and various characteristics or time periods are spread out across multiple columns.

Imagine a spreadsheet tracking monthly sales for a few products:

This format is clean and easy for a human to scan. But ask yourself: how would you build a single chart in Power BI to show total sales trends over time? You’d have to manually drag each month's column into the chart. If you had 36 months of data, this would be a nightmare. Filtering by month becomes clunky, and running time-based calculations is nearly impossible.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

"Tall" Data: How Power BI Prefers It

Tall data organizes the same information differently. Instead of adding new columns for each new category or time period, it adds new rows. It has fewer columns but many more rows.

Here’s how the same sales data looks in a "tall" format:

This might look less concise to our eyes, but for Power BI, it's perfect. Now you have a single column for "Month" and a single column for "Sales." Creating reports becomes incredibly simple:

• To build a line chart: Drag "Month" to the X-axis and "Sales" to the Y-axis. Done.
• To filter by product: Use the "Product" column as a slicer.
• To see Q1 sales: Filter the "Month" column for Jan, Feb, and Mar.

This structure is the foundation of good reporting in virtually any BI tool. The process of converting wide data into tall data is called unpivoting, and it is one of the most common and important steps in data preparation.

Unpivoting: Transforming Wide Data into Tall Data

Unpivoting takes your attribute columns (like "Jan Sales," "Feb Sales," etc.) and converts them into two new columns: one for the attribute itself (the month) and one for its corresponding value (the sales amount).

When Should You Unpivot Data?

You’ll reach for the unpivot tool anytime your data has repeating categories or time periods spread across columns. Common scenarios include:

• Sales or financial reports with columns for each month, quarter, or year.
• Survey results where each question is its own column, and the cells contain the responses.
• Product or feature comparisons with a column for each feature and a "Yes/No" or rating in the cells.
• Website analytics exports that might show metrics like sessions or conversions broken out by day in separate columns.

In all these cases, unpivoting makes the data usable for dynamic filtering and analysis in Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide: How to Unpivot Columns in Power Query

All data reshaping in Power BI happens in the Power Query Editor. Let’s walk through the process using our sample sales data.

1. Load Your Data & Open Power Query: First, get your data into Power BI Desktop. Then, on the Home tab, click Transform data to open the Power Query Editor.
2. Identify Your Anchor Columns: Look at your wide dataset. Some columns need to stay as they are. In our example, the "Product" column is our anchor - it identifies what each row is about. The columns we want to transform are "Jan Sales," "Feb Sales," "Mar Sales," and "Apr Sales."
3. Select the Anchor Columns: Click on the header of the "Product" column to select it. If you have multiple anchor columns (e.g., "Product" and "Region"), hold down the Ctrl key to select them all.
4. Unpivot the Other Columns: Now for the easiest part. Right-click on the header of a selected anchor column. From the dropdown menu, choose Unpivot Other Columns.

Power Query will instantly transform the data, leaving your "Product" column intact and creating two new columns: "Attribute" and "Value."

1. Rename Your New Columns: The default names "Attribute" and "Value" aren't very descriptive. Double-click the header of the "Attribute" column and rename it to something meaningful, like "Month." Do the same for the "Value" column and rename it to "Sales."

That’s it! Your data is now in a clean, tall format, ready for analysis. Click "Close & Apply" in the top-left to load your transformed data into your Power BI model.

Pro Tip: You might notice other unpivot options like "Unpivot Columns" and "Unpivot Selected Columns." While these work, "Unpivot Other Columns" is generally better. If new monthly data columns (e.g., "May Sales") are added to your source file later, your query will automatically unpivot them when you refresh your data, making your report future-proof.

Pivoting: Turning Tall Data into Wide Data

As you might guess, pivoting is the exact opposite of unpivoting. It takes two columns (an attribute and a value) and creates multiple new columns based on the unique values in the attribute column.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

When Would You Use Pivoting?

While unpivoting is a daily-use warrior for data analysts, pivoting is more of a specialized tool inside the Power Query Editor. You’ll use it less often for building analytical models but might find it useful in specific scenarios:

• Creating a Summary Matrix: If you need to export a specific cross-tab summary (a matrix-style report) for use outside of Power BI.
• Data Cleaning: Sometimes data comes in a messy tall format, and pivoting it can help organize it for a different transformation step.
• Specific Visual Requirements: Rarely, a particular custom visual or reporting need might demand data in a wide format to function correctly.

Generally, you want to avoid pivoting data that you plan to analyze with standard Power BI visuals, as it brings back all the problems that unpivoting solves.

Step-by-Step Guide: How to Pivot Columns in Power Query

Let's imagine we wanted to reverse our previous work and turn our tall sales data back into a wide table.

1. Open Power Query: Navigate back to the Power Query Editor with your tall data loaded.
2. Select the Attribute Column: Select the column whose values you want to become the new column headers. In our case, this is the "Month" column.
3. Find the Pivot Column Tool: With the "Month" column selected, go to the Transform tab in the ribbon. In the "Any Column" group, click Pivot Column.
4. Configure the Pivot Options: A dialog box will appear.
5. Click OK: Power Query will now pivot the data, creating separate columns for each month and putting the corresponding sales figure in the cells, recreating your original wide table.

Final Thoughts

Mastering unpivoting is a non-negotiable skill for becoming proficient in Power BI. It’s the key to transforming common spreadsheet-style data into a format that powers insightful, interactive reports. While pivoting is used less frequently, understanding how it works gives you a complete toolkit for reshaping your data no matter the starting format.

We built Graphed because we believe valuable business insights shouldn't be locked behind tedious data preparation steps. While unpivoting in Power Query is powerful, it's still a manual process you repeat across all your data sources. Our goal is to automate this grunt work. We allow you to connect all your marketing and sales data, and then you can simply ask for what you want to see - "show me a trend of sales by product source for last quarter" - and our AI data analyst builds the dynamic dashboard in real-time, handling all the necessary transformations behind the scenes.