How to Change Columns to Rows in Power BI Table

Your data is in a table, but the layout is working against you. Instead of a neat row for each transaction or record, you have your data spread wide across multiple columns - maybe sales figures for January, February, and March each in its own column. It looks fine in a spreadsheet, but for building powerful visualizations in Power BI, it's a major roadblock. This article will show you exactly how to solve this common problem by transforming columns into rows 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

Why Would You Need to Change Columns to Rows?

This process, officially known as "unpivoting," is one of the most fundamental data-shaping tasks in business intelligence. The goal is to turn "wide" data into "long" data. But what does that mean?

• Wide Data: This is a common format in manually-maintained spreadsheets. Each subject (like a product or customer) has its own row, but attributes or time-series data are spread across many columns. For example, a table with columns for 'Product', 'Jan_Sales', 'Feb_Sales', 'Mar_Sales', etc.
• Long Data: This is the ideal format for most analytics tools, including Power BI. Each observation gets its own row. The same data from the wide example would have just three columns: 'Product', 'Month', and 'Sales'.

Let's look at a simple example.

Here’s a typical "wide" table:

If you wanted to create a simple line chart of sales over time, this structure is difficult to use. You’d have to manually add each quarter's sales column as a separate value, which is inefficient and doesn't scale well.

Here’s the same data in a "long" format:

This "long" format is much more flexible. You can easily drag 'Quarter' onto your X-axis and 'Sales' onto your Y-axis to create a dynamic chart. Adding filters or slicers for 'Product' is straightforward. This structure is the key to unlocking Power BI's reporting capabilities.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step Guide: Unpivoting Columns in Power BI

The magic happens inside the Power Query Editor, Power BI's built-in data transformation tool. It might sound technical, but follow these steps, and you'll see just how simple it is.

Step 1: Open the Power Query Editor

Once you've loaded your data into Power BI Desktop, look at the Home tab on the ribbon at the top. You'll see an option called Transform data. Clicking this will launch the Power Query Editor in a new window, which is where you can reshape and clean your data before building reports.

Step 2: Select the Columns to Unpivot

Here you have a critical choice to make, and it can save you a lot of future headaches. You need to tell Power Query which columns you want to turn into rows. There are two primary ways to do this:

Method A: Select the Columns You Want to Transform

Hold down the Ctrl key and click on each of the column headers that you want to unpivot. For our example, you would click on 'Q1_Sales', 'Q2_Sales', 'Q3_Sales', and 'Q4_Sales'.

When to use this: This method is fine for a one-off transformation with a fixed number of columns that won't change.

Method B (Recommended): Select the Columns You Want to Keep as Is

This is often the smarter and more scalable approach. Instead of selecting the columns to change, you select the columns that should remain as columns (your "anchor" columns). In our sales example, you would simply click the 'Product' column.

When to use this: Use this method when new columns might be added to your source data later (e.g., 'Q5_Sales' or survey data for a new month). It makes your report refresh-proof, as Power Query will automatically unpivot any new columns without you having to edit the query.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Choose "Unpivot Columns" from the Ribbon

With your columns selected, navigate to the Transform tab in the Power Query Editor's ribbon. Look for the Unpivot Columns button. Clicking the small dropdown arrow reveals a few options:

• Unpivot Columns: Use this command if you followed Method A and selected the columns you want to transform.
• Unpivot Other Columns: This is the powerful one. Use this command if you followed Method B and selected the anchor columns you want to keep. Power Query will unpivot everything else.
• Unpivot Only Selected Columns: This is functionally the same as the first option.

Choose the option that matches the selection method you used in Step 2. Instantly, your wide table will be transformed into a long format.

Step 4: Rename and Tidy Up Your New Columns

Power Query will create two new columns with the default names "Attribute" and "Value." These aren't very descriptive, so you should immediately rename them.

1. Double-click the "Attribute" column header and rename it to something meaningful, like "Quarter" or "Month."
2. Double-click the "Value" column header and rename it to something like "Sales" or "Revenue."

It’s also an excellent time to check the data types for your new columns. Power Query is usually pretty smart, but it's good practice to verify. Click the icon to the left of the column name (e.g., an "ABC" for text or "123" for a whole number) to ensure "Quarter" is set to Text and "Sales" is set to a number format like Decimal Number or Currency.

Step 5: Click "Close & Apply"

Once you are happy with the transformation, go back to the Home tab in the Power Query Editor and click the Close & Apply button in the top-left corner. This will save your data transformation steps, close the editor, and load your newly structured data into your Power BI model, ready for analysis and visualization.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Common Pitfalls and Best Practices

Unpivoting is straightforward, but a few best practices can help you avoid common issues and build more robust reports.

• Always Prefer "Unpivot Other Columns": As mentioned before, this makes your reports far more resilient. If your source data adds a 'Q5_Sales' column next quarter, you won't need to change anything, your query will absorb it automatically during the next refresh. This is a classic "set it and forget it" technique.
• Rename Your Steps: On the right side of the Power Query Editor is a panel called "Applied Steps." Each transformation you make is listed as a step. By default, your unpivot step might be called "Unpivoted Other Columns." Right-click it and rename it to something like "Unpivoted quarterly sales columns." If you ever need to troubleshoot your query, this small act makes a world of difference.
• Verify Data Types immediately: Trying to sum a column of sales figures formatted as text will only lead to errors and frustration back in the report view. Always make sure your numbers are numbers, your text is text, and your dates are dates.
• Don't Be Afraid to Mix Methods: Sometimes you may have multiple groups of columns to unpivot. For instance, you might have columns for Quarter 1 Sales and Quarter 1 Units. You can unpivot them in separate steps to maintain clarity in your final data model.

Getting your data structure right in Power Query is 90% of the battle. The effort you spend here makes creating dashboards and deriving insights dramatically easier.

Final Thoughts

You’ve learned that "wide" data in Power BI can be a real headache but that fixing it is simple with the "Unpivot Columns" feature in Power Query. Restructuring your data from wide to long is a fundamental skill that transforms clunky tables into a flexible foundation for building effective, scalable, and insightful reports.

While mastering Power Query is incredibly powerful, it's also a great example of the time investment required for traditional BI tools. We built Graphed because we believe the process of getting from raw data to actionable insight should be much faster. Rather than clicking through transform menus, selecting columns, and tidying up data types, you simply connect your data sources and ask questions in plain English, like "show me our total sales by quarter and product." Graphed builds the real-time dashboard you need in seconds, handling all the necessary transformations behind the scenes so you can focus on the insights, not the setup.