How to Unpivot in Power BI

One of the biggest hurdles in data analysis isn't building the charts but getting your data into the right shape first. If your data is arranged with categories or time periods spread across multiple columns, you need to "unpivot" it before you can analyze it effectively in Power BI. This article provides a step-by-step guide on how to unpivot your data in Power BI’s Power Query Editor to make it analysis-ready.

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

What Does "Unpivot" Actually Mean?

Unpivoting is the process of transforming data from a "wide" format to a "tall" or "narrow" format. Think of it as taking your column headers and turning them into values within a single new column.

Most business intelligence tools, including Power BI, work best with data in a tall format. This structure is much easier for the software to filter, aggregate, and slice through, which is essential for creating dynamic visuals and reports.

Let’s look at a simple example. Imagine you have a spreadsheet with monthly sales data for different products:

Before Unpivoting (Wide Format):

This format is easy for humans to read, but it's difficult to analyze in Power BI. For instance, what if you wanted to create a chart showing total sales over time? Or filter sales for just Q1? It's clunky because the "month," which is a single attribute, is split across four different columns.

After unpivoting, the data would look like this:

After Unpivoting (Tall/Narrow Format):

Now, you have a single column for "Month" and a single column for "Sales." With this structure, you can easily create charts, add slicers to filter by month, and write simple DAX measures to calculate total sales without any headaches.

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

When Should You Unpivot Your Data?

You’ll often find yourself needing to unpivot data that comes from crosstab reports, manual data entry in spreadsheets, or certain system exports. Here are a few common scenarios where unpivoting is the solution:

• Time-Based Columns: Just like our sales example, this is the most frequent use case. Data often has columns for Days, Weeks, Months, Quarters, or Years (e.g., "Sales 2021," "Sales 2022," "Sales 2023").
• Categorical Columns: When different categories of the same metric are split into separate columns. For example, a customer service report might have columns like "Phone Support Tickets," "Email Support Tickets," and "Chat Support Tickets." Unpivoting creates a "Support Channel" column and a "Ticket Count" column.
• Question & Answer Data: Data from surveys often has each question as a separate column header, with the answers as the values. Unpivoting this data would create one column for "Question" and another for "Answer."
• Metrics from Different Regions: You might get a report with columns like "North America Sales," "Europe Sales," and "Asia Sales." Unpivoting turns this into a "Region" column and a "Sales" column.

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

The magic happens inside Power Query, Power BI’s built-in tool for transforming and cleaning data. Follow these steps to unpivot your columns.

Step 1: Open the Power Query Editor

First, get your data into Power BI Desktop. Once the data is loaded, navigate to the Home tab on the main ribbon and click on Transform data. This will launch the Power Query Editor in a new window, showing you all your data connections.

Step 2: Select the Columns You Want to Unpivot

Now, look at your table. You need to decide which columns you want to turn from columns into rows. Using our earlier sales example, you would select the columns that represent months.

You can select multiple columns by holding down the Ctrl key and clicking on each column header you want to unpivot (e.g., Jan Sales, Feb Sales, Mar Sales, Apr Sales).

Step 3: Click 'Unpivot Columns'

With your columns selected, go to the Transform tab in the Power Query ribbon. In the "Any Column" group, you will see a button labeled Unpivot Columns. Click this button.

Instantly, Power Query will work its magic. The columns you selected will disappear and be replaced by two new columns: "Attribute" and "Value". "Attribute" will contain the former column headers (e.g., "Jan Sales"), and "Value" will contain the corresponding cell values (e.g., 100).

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 4: Rename Your New Columns

The default names "Attribute" and "Value" aren’t very descriptive. Best practice is to immediately rename them to something meaningful that you and your report viewers will understand.

To rename a column, simply right-click the column header and select Rename, or just double-click the header itself. In our example:

• Rename "Attribute" to "Month"
• Rename "Value" to "Sales"

Step 5: Verify Data Types

Power Query is usually good at detecting the appropriate data type, but it's always wise to double-check. Ensure your new "Value" column (Sales in our example) is set to a numeric type, like Whole Number or Decimal Number. Your "Attribute" column (Month) will typically be Text. You can change the data type by clicking the icon to the left of the column header.

Step 6: Close & Apply

Once you are happy with the transformation, click Close & Apply in the top-left corner of the Home tab. This will close the Power Query Editor and apply your changes to the data model in Power BI Desktop. Your data is now perfectly structured for dashboards and analysis!

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

Timesaving Tip: 'Unpivot Other Columns'

What if you have fifty monthly columns? Manually selecting all of them while holding 'Ctrl' would be tedious and error-prone. In this scenario, Power Query offers a much smarter option: Unpivot Other Columns.

Instead of selecting the columns you want to unpivot, you select the columns you want to keep. These are your anchor columns that shouldn't be changed.

Here’s how to do it:

1. In Power Query, select the column(s) that you want to keep as they are. In our example, you would only select the Product column.
2. Go to the Transform tab.
3. Click the dropdown arrow next to the Unpivot Columns button.
4. From the dropdown menu, select Unpivot Other Columns.

This tells Power Query to keep Product and unpivot everything else. It’s an incredibly efficient way to handle tables with a small number of static columns and a large number of columns that need to be unpivoted.

Common Mistakes to Avoid

Unpivoting is straightforward, but a few small missteps can cause confusion later. Here’s what to watch out for:

• Forgetting to Rename Columns: If you leave the columns named "Attribute" and "Value," your reports will be hard to understand. Always take a moment to give them meaningful names.
• Choosing the Wrong Data Types: If your numeric values are incorrectly identified as text, you won't be able to perform mathematical calculations like sums or averages. Always check your data types after a transformation.
• Leaving Unnecessary Prefixes/Suffixes: In our example, the month column contained values like "Jan Sales," "Feb Sales," etc. For cleaner analysis, you could use "Replace Values" or "Split Column" in Power Query to remove the word "Sales" and just keep the month names.

Final Thoughts

Learning how to unpivot your data is a fundamental skill that moves you from a beginner to an intermediate Power BI user. It solves one of the most common data structure problems and unlocks the full analytical power of the tool by transforming messy, wide data into clean, tall tables ready for visualization.

All these data transformation steps in tools like Power BI are necessary because most data sources aren't built for easy analysis right out of the box. At Graphed, we handle all this for you automatically. We built our platform to connect directly to sources like Google Analytics, Shopify, Facebook Ads, and Salesforce and pull the data in a pre-cleaned, analysis-ready format. This lets you skip the tedious manual cleaning and unpivoting steps, so you can go straight to asking questions in plain English and get real-time dashboards in seconds, without ever needing to open a tool like Power Query.