How to Switch Rows and Columns in Power BI

Wrangling your data into the right shape is one of the most common tasks you'll face in Power BI. If your data isn't structured correctly, building effective reports and dashboards becomes nearly impossible. This article walks you through the essential techniques for switching rows and columns in Power BI using the Power Query Editor, from simple table flips to more advanced data restructuring.

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

Why Would You Need to Switch Rows and Columns?

Before jumping into the "how," let's quickly cover the "why." You'll often receive data that’s formatted for human readability in a spreadsheet, not for analysis in a BI tool.

Imagine getting a sales report where products are listed in rows, but each month's sales figures are in separate columns ("Jan Sales," "Feb Sales," "Mar Sales," and so on). This "wide" format is easy for a person to scan, but terrible for Power BI. To properly analyze trends over time, you need a "long" format with a single "Month" column and a single "Sales" column.

This process of reshaping data is fundamental to data analysis. By learning how to transpose, unpivot, and pivot your data, you can clean and prepare almost any dataset for powerful, flexible reporting.

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

Method 1: The Quick Flip with "Transpose"

The "Transpose" function is your go-to for a straightforward swap of a table's entire set of rows and columns. It's fast and effective for simple datasets where you literally want to flip the entire table on its side.

When to Use Transpose:

• Your data is very simple, and the whole table needs to be rotated.
• For example, you have a small table with metrics in the first column and their values in the second, and you want the metrics to become column headers.

Step-by-Step Guide to Transpose:

Let's say you have a small table of website metrics like this:

For a specific type of report, you might want this data to be horizontal. Here’s how you transpose it.

1. Open Power Query Editor: In the Power BI Desktop main window, click on the Home tab ribbon, then click Transform data. This will launch the Power Query Editor in a new window.
2. Select Your Table: On the left side of the Power Query Editor, in the Queries pane, select the table you want to modify.
3. Find the Transpose Function: Navigate to the Transform tab in the ribbon at the top of the window.
4. Click "Transpose": In the "Table" group, you'll see a button labeled Transpose. Click it.

Instantly, your table will flip:

Wait, that's not quite right. After transposing, you'll often need to clean it up.

Post-Transpose Cleanup: Promoting Headers

You’ll notice the actual metric names are now in the first row, not the headers. Power BI has a button for that.

1. Navigate to the Home tab (or the Transform tab).
2. Click Use First Row as Headers.

Now your table looks perfect:

While Transpose is simple, it's a blunt instrument. For more sophisticated restructuring, you need a different tool.

Method 2: Restructure Data Like a Pro with "Unpivot Columns"

"Unpivoting" is one of the most powerful and common transformations you'll perform. It converts data from a "wide" format to a "long" format, which is the ideal structure for analysis in Power BI.

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

When to Use Unpivot:

• You have multiple columns that represent the same type of value (e.g., sales figures for different months, regions, or product categories).
• You want to create measures or charts that group and filter by those column headers (e.g., analyze sales trends over time, or compare regions).

Step-by-Step Guide to Unpivot Columns:

Let's use our earlier example: a table of product sales where each month is a separate column.

Our goal is to create three columns: Product, Month, and Sales.

1. Load Data and Open Power Query Editor: Make sure your table is loaded into Power BI and that you have opened the Power Query Editor ("Transform data").
2. Select the Stationary Columns: Instead of selecting all the columns you want to unpivot (Jan_Sales, Feb_Sales, etc.), it's often easier to select the column(s) you want to keep as they are. In this case, click the header for the Product column to select it.
3. Right-Click and Choose "Unpivot Other Columns": With the Product column selected, right-click on its header. From the context menu, choose Unpivot Other Columns.

Power Query will work its magic, and your table will instantly be reshaped:

Post-Unpivot Cleanup: Rename and Refine

The structure is right, but the column names are generic.

1. Rename Columns: Double-click the header named Attribute and rename it to something descriptive, like Month. Do the same for the Value column, renaming it to Sales.
2. Clean Up Values (Optional): Your new Month column contains values like "Jan_Sales". You might want to clean this up. Select the Month column, go to the Transform tab, and use the Replace Values tool to replace "_Sales" with nothing (leave the "Replace With" field blank).
3. Check Data Types: Power Query is smart, but it's always a good idea to check your data types. Make sure the new Sales column is set to a number type (like Whole Number or Decimal Number) and your Month column is Text. You can change data types by clicking the icon on the left side of the column header.

Your final, analysis-ready table now looks like this:

This "long" format is now perfect. You can easily build a line chart with 'Month' on the axis and 'Sales' as the values, and use 'Product' as a legend or slicer.

Method 3: The Reverse Operation with "Pivot Column"

As you might guess, "Pivoting" is the opposite of unpivoting. It takes a "long" format table and transforms it into a "wide" one, turning unique values from one column into new individual columns.

When to Use Pivot:

• You want to create a summarized matrix table for a report where each row represents an item and the columns represent categories.
• You're trying to prepare data for an export that requires a specific "wide" table format.
• You need to perform calculations between values that are currently in different rows (e.g., calculate the difference between January and February sales).

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-by-Step Guide to Pivot a Column:

Let's take the "long" table we just created in the unpivot section and turn it back into its original "wide" format.

Our starting "long" table:

1. Select the Column to Pivot: In the Power Query Editor, select the column whose values you want to become the new column headers. In our case, this is the Month column.
2. Choose the "Pivot Column" Function: Go to the Transform tab. In the Any Column group, you will find the Pivot Column button. Click it.
3. Configure the Pivot Options: A dialog box will appear. This is where you tell Power Query how to build the new pivoted table.
4. Click OK: Power Query will now use the unique values from your Month column to create new columns, populating them with the corresponding summed values from your Sales column.

And just like that, you are back to the wide format. This demonstrates the powerful, symbiotic relationship between pivoting and unpivoting data.

Final Thoughts

Mastering tools like Transpose, Unpivot, and Pivot within Power BI's Power Query Editor transforms you from a simple report builder into a capable data shaper. These functions are the key to unlocking messy, poorly structured data and preparing it for meaningful analysis, turning raw spreadsheets into insightful dashboards.

While these transformations within Power BI are incredibly powerful, they still involve a multi-step process for each data source. We built Graphed because we wanted to automate this entire data-wrangling process. Instead of manually clicking through the Power Query editor to unpivot columns or merge tables, you can just ask a question like, "Show me product sales by month for the first quarter." Graphed connects to your live data sources and automatically generates the interactive dashboard, handling all the complex data shaping behind the scenes so you get straight to the insights.