What is Unpivot Columns in Power BI?

Dragging your spreadsheet into Power BI only to realize your data is structured completely wrong for analysis can be incredibly frustrating. If your data has information spread out across columns - like monthly sales, survey questions, or product attributes - you're working with "wide" data, a format that clashes with how powerful reporting tools prefer to work. This article will show you how to fix this common problem using the Unpivot Columns feature in Power BI, transforming your stubborn dataset into a flexible and analysis-ready format.

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

Understanding "Wide" vs. "Long" Data

Before diving into the "how," it's crucial to understand the "why." Business intelligence tools like Power BI are designed to work most efficiently with data in a "long" (or "tall") format, not a "wide" one. Let's break down the difference with a simple, familiar example: tracking product sales by month.

The "Wide" Data Format

Most people naturally record data in a wide format in spreadsheets. It’s readable for humans because we can easily scan across rows. For our example, a wide format would have a separate column for each month.

Here’s what that looks like:

At first glance, this seems perfectly fine. The problem arises when you want to analyze it in Power BI. How would you:

• Create a filter or slicer to let users select a specific month?
• Build a chart showing total sales trends over time?
• Calculate the total sales for the entire year without writing a complicated formula like [Jan_Sales] + [Feb_Sales] + ...?

You’d find yourself hitting a wall. Worse, if you add "May_Sales" next month, you have to update all your reports and formulas to include the new column. It's not scalable.

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

The "Long" Data Format

The "long" data format is the solution. It structures the same information by dedicating columns to specific categories of data. Instead of spreading months across columns, we create a single "Month" column and a single "Sales" column.

Here’s the same data in a long format:

Although this table has more rows, it's far more powerful for analysis:

• Building a monthly filter is now easy - you just use the "Month" column.
• Creating trend charts is simple because all sales values are in a single "Sales" column.
• Calculating total sales is a breeze - just sum the "Sales" column.

When May data arrives, you just add new rows. Your reports and formulas will update automatically. This structure is efficient, scalable, and what Power BI expects.

What is "Unpivoting Columns"?

Unpivoting is the official term for this transformation process - turning columns into rows. It takes the column headers from your wide table (like "Jan_Sales", "Feb_Sales") and turns them into values in a new "Attribute" column. Then, it takes the values from under those headers (the actual sales figures) and places them in a new "Value" column.

Essentially, you are taking a horizontal dataset and making it vertical.

This entire process happens inside the Power Query Editor, which is Power BI's data transformation engine that runs behind the scenes. You shape your data in Power Query before it gets loaded into your Power BI report for visualization.

A Step-by-Step Guide to Unpivoting Columns in Power Query

Ready to try it out? Let's walk through unpivoting our "wide" sales data into the "long," analysis-ready format.

Step 1: Open the Power Query Editor

Once you’ve loaded your data into Power BI Desktop, the first step is to open Power Query. You can do this by clicking the Transform Data button on the Home ribbon.

This will launch a new window - the Power Query Editor - where you can see a preview of your dataset and perform all your data cleaning and transformation steps.

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: Identify and Select Your Columns

Now, look at your data preview. You need to decide which columns you want to unpivot. There are two main approaches here, and choosing the right one can save you a lot of time later.

Method 1: Unpivot Selected Columns

This method is straightforward. You manually select all the columns you want to transform into rows.

1. Click on the header of the first column you want to unpivot (e.g., Jan_Sales).
2. Hold down the Ctrl key and continue clicking the headers of all the other columns you want to unpivot (Feb_Sales, Mar_Sales, Apr_Sales).
3. Once all the desired columns are selected (they will be highlighted), navigate to the Transform tab in the ribbon.
4. Click the Unpivot Columns button.

That's it! Power Query will transform those selected columns into the two new "Attribute" and "Value" columns. This method is great when you have a fixed and easily selectable set of columns.

Method 2: Unpivot Other Columns (Recommended)

This method is more robust and generally the preferred approach because it's more scalable. Instead of selecting the columns you want to change, you select the columns you want to keep as they are.

Why is this better? Imagine your data source gets an update, and now includes a "May_Sales" column. If you used "Unpivot Selected Columns," your query would ignore this new column, and you wouldn't see May's data. You'd have to edit the query manually.

With "Unpivot Other Columns," Power Query unpivots everything except for the columns you told it to keep. The new "May_Sales" column will be automatically unpivoted next time you refresh. Here’s how to do it:

1. Select the columns you want to remain as is. In our example, Ctrl + Click the headers for Product Name and Category.
2. Navigate to the Transform tab.
3. Click the small dropdown arrow next to the "Unpivot Columns" button.
4. From the dropdown, select Unpivot Other Columns.

Just like that, Power Query keeps "Product Name" and "Category" and unpivots all other columns, delivering the same result as Method 1 but with added flexibility for the future.

Step 3: Rename Your New Columns

After unpivoting, Power Query creates two new columns named "Attribute" and "Value" by default. These names aren't very descriptive, so you should always rename them.

• Attribute: This column now holds your original column headers (e.g., "Jan_Sales", "Feb_Sales"). A better name would be "Month".
• Value: This column holds the corresponding values from your original dataset (e.g., 1500, 1750). "Sales Amount" would be a much clearer name.

To rename a column, simply double-click its header and type the new name, then press Enter.

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 4: Check Data Types and Apply Changes

Lastly, it’s good practice to check that Power Query assigned the correct data types to your new columns. In the column header, you’ll see an icon indicating the data type (e.g., ABC for text, 123 for whole number, 1.2 for decimal, etc.). Ensure your "Sales Amount" column is a number type and "Month" is text. If they're wrong, just click the icon and select the correct type from the list.

When you're happy with the changes, click the Close & Apply button in the top-left corner of the Home ribbon. This will close the Power Query Editor and load your newly shaped data into your Power BI Desktop model, ready for creating reports and visualizations.

More Use Cases for Unpivoting

Once you get the hang of it, you'll start seeing opportunities to unpivot data everywhere. The technique isn’t just for monthly data. Consider these common scenarios:

• Survey Results: Imagine a spreadsheet where each row is a respondent, and each column is a question (Q1, Q2, Q3, etc.). Unpivoting this data would give you a neat, three-column table: Respondent ID, Question, and Answer, which is much easier to analyze.
• Metrics Reporting: You might have a report with Dates as rows and different KPIs as columns (Impressions, Clicks, Conversions). By unpivoting the KPI columns, you’d get Date, Metric Type, and Value columns, allowing you to easily filter and compare different metrics on a single chart.
• Product Attributes: An export from your e-commerce system might have products in rows and their features spread across columns like Color, Size, Material. Unpivoting Color, Size, and Material would give you two attribute columns - Feature Type and Feature Value - making it easy to filter products by their attributes.

Final Thoughts

Learning how to use Power BI's Unpivot Columns feature is a critical step in mastering data preparation. By reshaping your 'wide' data tables into a 'long' format, you make them more scalable and unlock the full analytical power of your reporting tools, leading to more dynamic filters, simpler formulas, and more insightful visuals.

While Power Query is a powerful tool for this kind of data wrangling, all these transformation steps need to be set up manually for each new project. At Graphed, we felt this entire analysis process could be vastly streamlined. Our platform connects directly to your data sources and uses AI to handle the necessary transformations for you. You just ask for a report in plain English - like "show my Shopify sales trend by month compared to last year" - and Graphed automatically generates the dashboard, without you ever having to open a query editor or unpivot a single column.