Power bi rows to columns unpivot pivot complete guide

Admin

May 29, 2026

Direct Answer

To convert rows to columns in Power BI, use Pivot Column in Power Query Editor (Transform tab → Pivot Column). To convert columns to rows, use Unpivot Columns (select columns → right-click → Unpivot Columns). Both are data model transformations — they reshape the data before it loads into the report. For dynamic row/column switching in the published report without reshaping the data model, use a Matrix visual or Flexa Tables.

Pivot vs Unpivot: Which Direction Do You Need?

These two operations are opposites. The confusion usually comes from source data that arrived in the wrong shape for Power BI's data model requirements.

PIVOT — rows → columns

Takes unique values from one column and turns each into a new column header. Wide format output.

When you need it: Source data has a "Month" column with values Jan/Feb/Mar and you want Jan, Feb, Mar as separate columns in a report table.

UNPIVOT — columns → rows

Takes multiple column headers and collapses them into a single column with a new value column alongside. Long format output.

When you need it: Source data has Jan, Feb, Mar as separate columns (often from Excel) and you need them as rows so DAX time intelligence can work.

⚠ Power BI data model prefers long format (unpivoted). If your source has months as columns, you almost always want to unpivot before loading — this lets DAX time intelligence, slicers, and date relationships work correctly. Wide format (pivoted) is typically for the report output layer, not the data model layer.

How to Unpivot Columns in Power BI (Columns → Rows)

The most common use case: Finance sends a budget spreadsheet with months as column headers. You need to unpivot it before the data model can work with it.

Source data (wide format — problematic for Power BI):

Account	Jan	Feb	Mar	Apr
Revenue	400K	380K	430K	450K
COGS	180K	170K	195K	200K

After unpivot (long format — correct for Power BI):

Account	Month	Amount
Revenue	Jan	400K
Revenue	Feb	380K
Revenue	Mar	430K
COGS	Jan	180K

Step-by-step in Power Query:

Click Transform Data in the Home ribbon to open Power Query Editor
Select the columns you want to keep as-is (e.g. Account) — hold Ctrl to select multiple
Right-click the selected columns → Unpivot Other Columns (this unpivots everything except what you selected)
Power Query creates two new columns: Attribute (column headers) and Value (cell values)
Rename "Attribute" to "Month" and "Value" to "Amount" — click the column header to rename

// M code generated by Power Query (reference only)
= Table.UnpivotOtherColumns(
    Source,
    {"Account"},      // columns to keep as-is
    "Month",          // name for the new attribute column
    "Amount"          // name for the new value column
  )

Use "Unpivot Other Columns" (not "Unpivot Columns") whenever new months may be added to the source — this approach dynamically handles any new column that appears without breaking the query.

How to Pivot in Power BI (Rows → Columns)

Less common than unpivot for Power BI data models, but needed when source data is already in long format and a specific report requires a wide format table — for example, a comparison table where each product is a column.

Step-by-step:

In Power Query, select the column whose values will become new column headers (e.g. "Region")
Go to Transform tab → Pivot Column
In the dialog, select the Values column (e.g. "Sales Amount")
Choose the Aggregate Function — Sum for numeric data, Don't Aggregate for text
Click OK — each unique value in the Region column becomes a new column

⚠ Pivot in Power Query creates hardcoded columns. If a new region appears in the source data next month, the pivot step will not automatically add a new column — you need to refresh and the query will error or add null columns. This is why pivot at the data model layer is fragile. For dynamic cross-tab display, use a Matrix visual instead (which pivots at the report layer, not the data layer).

When to Use Matrix Instead of Pivoting in Power Query

Most "rows to columns" requirements in reporting do not actually need a Power Query pivot. The Matrix visual achieves the same visual output — regions as columns, months as rows, values in cells — without touching the data model.

Use Power Query Pivot when:

Source system exports in long format but a downstream process requires wide format
You need the pivoted structure for a relationship or merge in the data model
The column names are fixed and will never change

Use Matrix visual when:

You want cross-tab display in the report (rows × columns = value)
Column values may grow dynamically (new months, new regions)
You need subtotals, drill-down, or conditional formatting

The Matrix Visual Limitation That Sends People Back to Excel

The Matrix visual is great for fixed cross-tab layouts. The problem: once published, end-users cannot change which field is on rows and which is on columns, cannot add new fields to the view, and cannot reorder columns — all require the developer to open Desktop and republish.

This is why Finance teams often export the Matrix to Excel to do the pivoting themselves — the report output is right but the interactivity isn't. For reports where users need to pivot the data themselves (swap rows and columns, add fields, restructure the view) inside Power BI Service, see Power BI Pivot Table: Add Excel-Style Pivoting Without DAX and Power BI Matrix vs Table: When to Use Each.

Decision Guide: Which Approach to Use

Your situation	Recommended approach
Source has months as columns, need long format for DAX	Unpivot Other Columns in Power Query
Source is long format, need wide for a specific table	Pivot Column in Power Query (fixed column names only)
Cross-tab report, column values grow dynamically	Matrix visual (report layer, not data layer)
End-users need to swap rows/columns after publish	Flexa Tables
Replace Excel pivot table inside Power BI Service	Flexa Tables

Let Users Pivot the Report Themselves

Flexa Tables installs from AppSource in under 5 minutes. Free trial, no credit card required.

Get Free Trial on AppSource →

Frequently Asked Questions

How do I convert rows to columns in Power BI?

Open Power Query Editor (Transform Data in Home ribbon), select the column whose values should become column headers, go to Transform tab → Pivot Column, select the Values column in the dialog, choose Sum as the aggregate function, and click OK. Each unique value in the selected column becomes a new column header.

How do I unpivot columns in Power BI?

In Power Query Editor, select the columns you want to keep as-is (e.g. Account, Date), right-click → Unpivot Other Columns. Power Query collapses all other columns into two new columns: Attribute (original column headers) and Value (cell values). Rename these to meaningful names (e.g. Month and Amount). Use "Unpivot Other Columns" rather than "Unpivot Columns" so new source columns are handled automatically.

Why should I unpivot data before loading into Power BI?

Power BI's data model and DAX time intelligence functions work best with long format data — one row per record with a single date column. Wide format data (months as columns) cannot connect to a Date dimension table, cannot be used with DATEADD or SAMEPERIODLASTYEAR, and cannot be filtered with a date slicer. Unpivoting before loading resolves all of these issues.

What is the difference between Pivot Column and Unpivot Columns in Power Query?

Pivot Column converts row values into column headers — wide format output. Unpivot Columns converts column headers into row values — long format output. They are inverse operations. In Power BI data modeling, Unpivot is far more commonly needed because source data (especially from Excel and ERP systems) arrives in wide format but the data model requires long format.

Can I swap rows and columns in a Power BI report after publishing?

Not with the native Matrix visual — the layout is fixed at design time and requires the developer to republish any structural change. For reports where end-users need to swap rows and columns, add fields, or restructure the view themselves in Power BI Service, Flexa Tables provides drag-and-drop pivot control without developer involvement.