How to Transpose Data in Power BI

Transforming your data from columns to rows, or vice-versa, is a fundamental step in preparing your data for analysis in Power BI. Whether you have a report where dates are listed across the top or categories are laid out horizontally, known as a "wide" format, you'll need to reshape it. This article walks you through exactly how to transpose your data using Power BI's Power Query Editor.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly Is Transposing Data?

In the simplest terms, transposing data is like turning your table 90 degrees. It swaps the rows and columns. What was in your first row becomes your first column, and what was in your first column becomes your first row, and so on.

Imagine you have a small table of quarterly sales like this:

Before Transposing:

After a transpose operation, the table would look like this:

After Transposing:

The horizontal headers (Q1, Q2, Q3) are now vertical row values, and the vertical row values (Total Revenue, Units Sold) have become column headers. This simple flip is often the first step in cleaning up data that comes from crosstabs or pivot tables.

Why Would You Need to Transpose Data?

Data rarely arrives in a perfectly analysis-ready format. Reshaping it is a common part of the data preparation workflow for a few key reasons:

• Fixing Poor Data Structures: Data exported from financial systems, spreadsheets, or other software often comes in a wide format where time periods (like months or years) are column headers. For proper time-series analysis in Power BI, you need a single "Date" column, not twelve separate columns for each month.
• Preparing for Visualizations: Many Power BI charts, particularly line charts, work best when you have date or category columns to place on the X-axis and a single numerical column for the Y-axis values. Transposing or unpivoting helps get your data into this "long" format.
• Simplifying Calculations: Writing DAX formulas is much easier when your data is structured properly. Summing a single "Sales" column is simple. Trying to add up twelve different columns - [Jan Sales] + [Feb Sales] + [Mar Sales] - is inefficient and difficult to maintain.

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 Transpose a Table

The magic of transposing happens in the Power Query Editor, Power BI's built-in data transformation tool. It feels a bit like Excel on steroids, allowing you to record transformation steps that you can apply automatically every time your data refreshes.

Here’s how to do it step-by-step.

1. Open the Power Query Editor

First, you need to get your data into Power Query. From the main Power BI Desktop window, find the Home tab on the ribbon. Click the Transform data button. This will launch the Power Query Editor with your loaded tables listed in the left pane.

2. Select Your Table

In the list of queries on the left of the Power Query Editor, click on the table you want to modify.

3. Use the Transpose Command

With your table selected, go to the Transform tab in the Power Query ribbon. In the "Table" group, you will see a prominent button labeled Transpose. Simply click it.

That's it! Power Query instantly flips your entire table, swapping the rows and columns.

Graphed

Still Building Reports Manually?

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

Watch the Demo

4. Handle the Headers

This is the most common follow-up step. When you transpose a table, the original column headers become the values in the first row of your new, transposed table. The new column headers will be generic defaults like "Column1," "Column2," "Column3," etc., which isn't very useful.

To fix this, go back to the Transform tab. In the very first group, find a button called Use First Row as Headers. Clicking this will "promote" the values from the first row to be the column headers, giving you a properly labeled table.

A Better Way for Most Cases: Transpose vs. Unpivot

While transposing is a useful tool, for many real-world use cases, it's not actually what you need. Often, people search for "transpose" when they actually need to "unpivot" their data.

Let's explain the difference. Transposing flips the entire table. Unpivoting only flips a selection of columns into rows. This is much more practical for turning wide sales data into a proper tall table for analysis.

Let’s look at a common example. You have e-commerce data that looks like this:

Original Wide Data:

If you use Transpose on this table, you'll get a weird, unusable structure. What you actually want is a table with columns for Product, Category, Month, and Sales. This is a job for Unpivot Columns.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Unpivot Columns

1. In the Power Query Editor, select the columns you want to keep as they are. In our example, hold down the "Ctrl" key and click the headers for Product and Category.
2. With those columns selected, go to the Transform tab.
3. Click the dropdown arrow on the Unpivot Columns button.
4. Select Unpivot Other Columns. This keeps your selected columns (Product, Category) and unpivots everything else.

The result is a perfectly structured "long" table, ready for analysis:

After Unpivoting:

The last step is to simply rename the default "Attribute" column to "Month" and the "Value" column to "Sales." Now your data is ready for building powerful reports and dashboards.

Final Thoughts

Reshaping your data is a daily reality in data analysis. Power BI’s transpose function in the Power Query Editor provides a quick and simple way to flip your entire table, which can be useful for certain structural fixes. However, for the very common task of converting wide, human-readable reports into a long, machine-readable format, the Unpivot Columns command is a much more powerful and flexible tool to have in your arsenal.

Working through these data preparation steps in tools like Power BI is incredibly powerful, but it's still a manual process you have to learn and repeat. We built Graphed because we wanted to eliminate these repetitive tasks entirely. Instead of clicking through menus to transpose or unpivot, you can simply connect your data sources and ask a question in plain English like, "show me a monthly sales trend by product." Our AI handles the data modeling and transformation automatically, building the live dashboard for you in seconds, not hours.