How to Convert Row to Column in Power BI

One of the most common challenges in data analysis is that your data isn't always formatted in a way that’s ready for reporting. Specifically for Power BI, having long, narrow tables is almost always better than short, wide ones. This tutorial will walk you through exactly how to convert rows into columns in Power BI using the Power Query Editor, focusing on two key techniques: Transpose and Unpivot.

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 Convert Rows to Columns in the First Place?

Before jumping into the "how," let's quickly cover the "why." Power BI, like most analytics tools, works best with "long" or "normalized" data. This means you have fewer columns and more rows. A "wide" dataset does the opposite, spreading values across many columns instead of organizing them into a single category.

Imagine you have sales data organized like this:

Wide Data Format (Hard to Analyze)

Here, each month is its own column. If you wanted to create a simple line chart showing sales over time, it's surprisingly difficult. You’d have to manually add each month's column to the visual. And what happens when April's data comes in? You have to edit your report to add a new Apr_Sales column.

Now, let's look at the same data in a "long" format:

Long Data Format (Ideal for Power BI)

With this structure, analyzing your data becomes incredibly easy. You can drag the "Month" field to an axis and the "Sales" field to the values to see trends instantly. This format is scalable, flexible, and exactly what Power BI wants. The process of converting wide data into long data is what we’re about to do.

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

Enter the Power Query Editor

All data transformation in Power BI happens within the Power Query Editor. It’s a powerful tool that records your data-shaping steps so you can refresh your data without having to repeat the whole process.

To open it, click the "Transform data" button on the Home ribbon of Power BI Desktop. Every technique we discuss below will happen inside this editor.

Method 1: Using 'Transpose' for a Full Table Flip

The Transpose function is the most straightforward way to swap your rows and columns. It does exactly what it says: it takes your entire table and flips it on its side, turning every row into a column and every column into a row. Think of it as rotating your data 90 degrees.

When to Use Transpose

Transpose is best for specific situations where the entire dataset needs to be inverted. This is common with financial reports or spreadsheet formats where headers are in the first column and an item's attributes are spread across a single row.

For example, you might have data that looks like this:

+-----------------+----------+----------+ | Metric | Widget A | Widget B | +-----------------+----------+----------+ | Units Sold | 500 | 350 | | Revenue | $2500 | $1750 | | Customer Rating | 4.5 | 4.2 | +-----------------+----------+----------+

In this format, it's hard to analyze "Revenue" as a value. By transposing it, you can make the data usable for visuals.

Step-by-Step Guide to Transposing Data

1. Launch Power Query: With your data loaded into Power BI, click "Transform data" to open the Power Query Editor.
2. Select Your Table: In the left-hand pane, choose the query (table) you'd like to modify.
3. Navigate to the Transform Tab: At the top, you'll see a ribbon with several tabs. Click on "Transform".
4. Click 'Transpose': In the "Table" group, you'll see a button labeled "Transpose". Click it.

Your entire table will instantly flip. The preview will update to show you the result:

Promoting Headers after Transposing

You'll notice the old headers ("Metric", "Widget A", etc.) are now in the first row. You need to tell Power Query that this row should be the new set of column headers.

1. Go to the "Home" tab in the ribbon.
2. In the "Transform" group, click "Use First Row as Headers".

Your table will now be correctly formatted with proper headers ("Metric," "Units Sold," "Revenue," etc.). The last step is to check and correct the data types for each column to make sure numbers are numbers and text is text.

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

Limitations of Transpose

Transpose is a blunt instrument. It swaps everything and gives you no granular control. In our initial sales data example, transposing the table wouldn't produce the "long" format we need. For that, we need a more surgical tool.

Method 2: Using 'Unpivot' for Structured Transformation

Unpivoting is the most common and powerful way to transform wide data into long data. It doesn't flip the entire table. Instead, it takes a group of columns and turns them into attribute-value pairs, effectively converting those columns into rows.

This is the perfect tool for our initial sales data problem (where 'Jan_Sales', 'Feb_Sales', and 'Mar_Sales' were separate columns).

Step-by-Step Guide to Unpivoting Columns

Let's use our product sales example and get it into the ideal long format.

1. Identify Your Anchor Columns

First, look at your table and identify the columns that you want to keep as they are. In our "wide" sales data example, the ProductName column should remain unchanged. This is our anchor column.

2. Unpivot Other Columns (The Easiest Way)

The "Unpivot Other Columns" function is often the simplest and most scalable approach. It keeps your selected anchor columns and unpivots everything else.

1. Select Your Anchor Column(s): In the Power Query Editor, click on the header of the ProductName column to select it. If you have multiple anchor columns (e.g., ProductID and ProductName), hold down Ctrl and click each one.
2. Right-Click a Selected Header: Right-click on one of the selected column headers. A context menu will appear.
3. Choose 'Unpivot Other Columns': Click on this option. Alternatively, you can go to the "Transform" tab, click the "Unpivot Columns" dropdown, and select "Unpivot Other Columns."

Instantly, Power Query will get to work. The Jan_Sales, Feb_Sales, and Mar_Sales columns will disappear and be replaced by two new columns:

• Attribute: This column contains the names of the columns that were unpivoted (e.g., "Jan_Sales", "Feb_Sales").
• Value: This column contains the values that were in those original cells (e.g., 150, 200, 175).

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

3. Rename and Clean Up the New Columns

The final step is to make your new table easy to understand.

1. Rename the 'Attribute' column: Double-click the "Attribute" header and rename it to something descriptive, like "Month." You can further clean this column by using the "Replace Values" or "Split Column" features to remove the "_Sales" text, leaving just "Jan", "Feb", etc.
2. Rename the 'Value' column: Double-click the "Value" header and rename it to "Sales."
3. Check Data Types: Ensure the "Sales" column is formatted as a Decimal Number or Whole Number and the "Month" column is Text.

You now have the perfectly formatted "long" table we saw earlier, ready for any analysis or visualization you can dream of in Power BI.

When to Use 'Unpivot Selected Columns'

What if you only want to unpivot a specific subset of columns? In that case, instead of selecting the anchor columns, you would select the columns you want to convert into rows (e.g., Jan_Sales, Feb_Sales, Mar_Sales), then go to the "Transform" tab and click "Unpivot Columns" (or right-click and choose "Unpivot Columns").

While this works, the "Unpivot Other Columns" method is often more robust. If a new month's column (Apr_Sales) is added to your source data, that method will automatically include it in the unpivot step during your next data refresh. The "Unpivot Selected Columns" method would ignore it, forcing you to edit your query.

Final Thoughts

Learning how to properly shape your data by converting rows to columns is one of the most critical skills for any Power BI user. Using the Transpose function for simple table flips and the Unpivot feature for converting wide data to long data will save you countless headaches and unlock far more powerful reporting and analysis capabilities.

Sometimes, the biggest hurdle to getting clear insights is just prepping the raw data so your tools can understand it. We built Graphed to remove that friction entirely. By connecting directly to your sources like Google Analytics or Salesforce and using natural language to build dashboards, we handle the complex modeling and a lot of the transformation work automatically. This lets you skip spending time in menus like Power Query and focus directly on asking questions about your business and getting answers instantly.