What Does Unpivot Do in Power BI?

If you've spent any time with Power BI, you've probably encountered data that's easy for humans to read but a nightmare for computers to analyze. Often, this data is arranged in a "wide" format, with values spread across multiple columns. The solution is a powerful but sometimes confusing feature called "Unpivot Columns." This article will walk you through exactly what unpivoting does, why it’s so important for your reports, and how to use it step-by-step.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

So, What Exactly Does "Unpivot" Mean?

In the simplest terms, unpivoting transforms your data from a wide format to a long (or "tall") format. Let's imagine you have a spreadsheet tracking monthly product sales. A "wide" format, which is very common and intuitive for us to read, might look like this:

Before Unpivoting (Wide Format):

Notice how each month has its own separate column. While this is easy to glance at, it's terrible for analysis in Power BI. You can't easily create a line chart showing sales trends over time, nor can you easily calculate the total sales for the year without adding up each individual column manually.

Unpivoting takes those month columns (Jan Sales, Feb Sales, etc.) and turns them into rows. After unpivoting, the same data would look like this:

After Unpivoting (Long Format):

As you can see, we now have a single column for the "Month" and a single column for "Sales." All the sales values are consolidated into one column, and the context (which month they belong to) is in the adjacent "Month" column. This structure is what Power BI expects and prefers for almost all types of analysis and visualization.

Why Is Unpivoting So Important in Power BI?

Changing your data from wide to long might seem like a simple formatting trick, but it unlocks the true analytical power of Power BI. Here are the main reasons why this is a critical skill to learn.

1. It Makes Your Visualizations Flexible and Powerful

With data in a "long" format, you can build much better reports. Using our sales example, the single "Month" column allows you to:

• Easily create a line chart showing sales trends over time, with "Month" on the X-axis and "Sales" on the Y-axis.
• Use a slicer to filter your entire report by one or more months. This is impossible when months are separate columns.
• Group data by month or create hierarchies (like Quarter → Month) for drill-down analysis in your visuals.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. It Radically Simplifies DAX Formulas

Writing Data Analysis Expressions (DAX) is the heart of creating advanced calculations in Power BI. Unpivoting your data makes writing DAX exponentially easier.

• Without Unpivoting: To calculate total annual sales for Widget A, your formula would look like this: Total Sales = [Jan Sales] + [Feb Sales] + [Mar Sales] + [Apr Sales]... This is manual, tedious, and breaks the second a new month is added.
• With Unpivoting: You only have one "Sales" column. The formula becomes incredibly simple: Total Sales = SUM('Sales'[Sales]) This formula is clean, efficient, and automatically works no matter how many months or products you have.

3. It Improves Your Data Model's Performance

The Power BI engine (VertiPaq) is optimized for long, narrow tables rather than short, wide ones. Fewer columns mean the engine can compress the data more efficiently, leading to smaller file sizes and faster report performance. While this might not be noticeable with a tiny dataset, it makes a massive difference as your reports grow in complexity and size.

4. It Makes Your Report Future-Proof

Let's say in May, you add a "May Sales" column to your original wide data. If you didn't unpivot, you'd have to go back and manually update every single visual and DAX measure to include this new column. It's a maintenance nightmare waiting to happen.

However, if you set up an unpivot operation in Power Query correctly, you can make it dynamic. When you refresh your data, the new "May Sales" column will be automatically unpivoted and included in your report without you lifting a finger.

How to Unpivot in Power BI: A Step-by-Step Guide

The unpivot operation happens in the Power Query Editor, which is Power BI's data transformation layer. Here’s how to do it.

Step 1: Open the Power Query Editor

First, you need to get your data into Power BI. Once it's loaded, find the Home tab in the main Power BI Desktop window and click on the Transform data button. This will launch the Power Query Editor where all the data shaping magic happens.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Select the Columns to Unpivot

Now, locate the table (or query) you want to modify in the left-hand pane. You have two main ways to select columns for unpivoting, and choosing the right one can save you a lot of future headaches.

Method A: Select the Columns You Want to Unpivot

This method is straightforward. Hold down the Ctrl key and click on the header of each column you want to transform from a column into a row value. In our example, you would click on "Jan Sales," "Feb Sales," "Mar Sales," and "Apr Sales."

When to use this: This is best when you have an unchanging and small number of columns you want to unpivot.

Method B: Select the Columns You Don’t Want to Unpivot

This is often the better and more robust method. Instead of selecting the columns you want to transform, you select the columns you want to keep as they are. In our example, this would just be the "Product" column.

When to use this: This is ideal when you have many columns to unpivot (like 12 months, or 52 weeks) or when new columns might be added later (like a "May Sales" column). By telling Power Query to keep "Product" and unpivot everything else, any new month columns added in the future will be automatically included in the operation on refresh.

Step 3: Choose the "Unpivot" Command

Once your columns are selected, go to the Transform tab in the ribbon at the top of the Power Query Editor. In the "Any Column" group, you'll see a button labeled Unpivot Columns. Click the dropdown arrow on this button to see your options:

• Unpivot Columns: Use this if you used Method A (selecting the columns to transform).
• Unpivot Other Columns: Use this if you used Method B (selecting the columns to keep static).
• Unpivot Only Selected Columns: This is a more explicit version of the first option and achieves the same result.

Choose the correct option based on which selection method you used in Step 2. The transformation will happen instantly.

Step 4: Rename Your New Columns

Power Query will create two new columns with the default names "Attribute" and "Value." These are not very descriptive. Always rename them to something meaningful.

• In our example, "Attribute" holds the original column headers, so you should rename it to "Month."
• The "Value" column holds the sales numbers, so you should rename it to "Sales."

You can do this by simply double-clicking on the column header and typing the new name.

Step 5: Check and Adjust Data Types

As a final step, make sure Power BI has assigned the correct data type to your new columns. In front of each column header, there is a little icon (e.g., "1.2" for Decimal Number, "ABC" for Text). Power BI is usually good at guessing, but it’s always wise to check.

• Your "Sales" column should be a numeric type (like Decimal Number or Whole Number).
• Your "Month" column should be Text.

If they are incorrect, click the icon and select the proper data type from the list.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 6: Click "Close & Apply"

Once you are happy with the changes, go to the Home tab in Power Query and click Close & Apply. This will close the editor and load your newly shaped, analysis-ready data into your Power BI model.

More Examples: Where to Use Unpivot

Beyond simple monthly sales data, unpivoting is useful in many other common scenarios:

Survey Responses

Before: Imagine a table with a row for each respondent and columns for "Question 1," "Question 2," "Question 3," etc.

After Unpivoting: You get a clean table with three columns: "Respondent ID," "Question," and "Answer," which allows you to easily analyze responses per question.

Product Attributes

Before: You have a product table with columns like "Feature1," "Feature2," and "Feature3." This is difficult to filter or analyze.

After Unpivoting: You can create a structure with two columns: "Feature Type" (containing "Feature1," "Feature2," etc.) and "Feature Value," making it simple to count or view products by their features.

Attendance or Score Checklists

Before: A table listing students in rows and dates in columns, with a "Yes" or "No" for attendance.

After Unpivoting: You get a table with three columns: "Student," "Date," and "Attended." Now you can easily calculate perfect attendance or find each student's attendance percentage.

Final Thoughts

Unpivoting data is one of the most fundamental and valuable data transformation skills you can learn for Power BI. By converting wide, human-readable data into a long, machine-friendly format, you unlock the ability to create flexible visuals, write simple DAX, and build efficient, scalable reports that are easy to maintain.

We know that cleaning and preparing data is often the most time-consuming part of creating any dashboard. With our tool, Graphed , we’ve focused on simplifying this entire process. Instead of getting stuck in data shaping screens, you can connect your sources and just ask for the chart or dashboard you need in plain English. The goal is to get you from raw data to actionable insights faster, removing technical barriers so you can focus on making smarter decisions for your business.