Power BI Guide  ·  Updated July 2026  ·  12 min read  ·  Flexa Intel Team

Power BI Rows to Columns: Complete Pivot & Unpivot Guide (2026)

1. Pivot vs Unpivot — What Each Does and When to Use It

Pivot and unpivot are data reshaping operations. They don't change the data — they change the shape of the table. Understanding which direction you need is the most important step before opening Power Query.

2. How to Unpivot Columns to Rows (Power Query)

Unpivot is used when your source data arrives in wide format — one column per month, quarter, region, or scenario — and you need to reshape it into tall format before loading into the data model.

Step-by-step: Unpivot in Power Query

1. Open Power Query Editor. In Power BI Desktop: Home → Transform Data.
2. Identify the columns to unpivot — the columns whose headers will become row values. In a typical financial export, these are the month or quarter columns (Jan, Feb, Mar, Q1, Q2…).
3. Select the columns to unpivot. Click the first column header, then Ctrl+Click each additional column to select all. Alternatively, select the columns you want to keep as rows, then use "Unpivot Other Columns" — this is safer because it automatically handles new columns added to the source.
4. Unpivot. With columns selected: Transform → Unpivot Columns. Power Query creates two new columns: Attribute (containing the original column headers) and Value (containing the cell values).
5. Rename the new columns. Double-click Attribute and rename it to Month (or Quarter, Period, etc.). Double-click Value and rename it to Revenue (or the appropriate measure name).
6. Set correct data types. The Value column will likely be text — change it to Decimal Number or Whole Number (click the data type icon in the column header).
7. Close & Apply. Home → Close & Apply. The unpivoted table is now available in your data model.

Unpivot with multiple identifier columns

If your source table has multiple columns that should remain as row identifiers (e.g. Product AND Region AND Year, with month columns to unpivot), select all the identifier columns first, then use Transform → Unpivot Other Columns. This preserves all identifiers on each row of the unpivoted output.

// Power Query M code equivalent of Unpivot Other Columns:
= Table.UnpivotOtherColumns(
    Source,
    {"Product", "Region", "Year"},  // columns to keep as identifiers
    "Month",                          // new column for attribute names
    "Revenue"                         // new column for values
)

3. How to Pivot Rows to Columns (Power Query)

Pivot is used less frequently in Power BI than unpivot — most of the time, pivoting should happen at the visual layer (Matrix visual or Flexa Tables), not in the data model. Use Power Query Pivot when a downstream system or specific report layout requires a fixed wide table.

Step-by-step: Pivot Column in Power Query

1. Open Power Query Editor. Home → Transform Data.
2. Select the column whose values will become column headers. Click the column header — for a month pivot, this is the Month column.
3. Go to Transform → Pivot Column.
4. In the Pivot Column dialog: select the Values Column (the column containing the numbers, e.g. Revenue). Set Aggregate Value Function to Sum (or Don't Aggregate if values are unique per row).
5. Click OK. Power Query creates one column per unique value in the selected column.
6. Sort columns if needed. Power Query does not guarantee column order — you may need to reorder columns manually: right-click a column header → Move → To Beginning/End, or drag columns.
7. Close & Apply.

Controlling which values become columns

If you only want specific values to become columns — for example, only the last 12 months rather than all available months — filter the column before pivoting:

// Filter to last 12 months before pivoting
= Table.SelectRows(
    Source,
    each [Month] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -12)
)

Apply this filter step before the Pivot Column step in Power Query.

4. Pivot at the Visual Layer — Matrix and Flexa Tables

For most Power BI reporting use cases, you should not pivot in Power Query at all. Instead, keep the data model in tall format and let the visual generate the cross-tab layout dynamically. This gives you two options:

Option A — Matrix visual (fixed after publishing)

Place your row dimension in Rows, your column dimension (Month, Quarter, Scenario) in Columns, and your measure in Values. The Matrix generates a cross-tab at render time — no Power Query pivot needed. The limitation: the layout is locked after publishing. Users can filter with slicers but cannot restructure the rows and columns or add variance columns without going back to Desktop.

Option B — Flexa Tables (dynamic after publishing)

Flexa Tables is a Microsoft-certified custom visual that adds true drag-and-drop pivot behavior to published Power BI reports. Unlike the Matrix visual, users can restructure rows and columns, show and hide columns, and add variance columns (MoM, YoY, Actual vs Budget) directly in Power BI Service — without IT involvement, without Desktop, without republishing.

5. Decision Guide: Which Approach to Use

6. Common Errors and Fixes

Error: "Expression.Error: The column 'April' of the table wasn't found"

This happens when you used Pivot Column and a new unique value appears in the source data that wasn't there when you built the query. Power Query tries to reference a column that doesn't exist yet. Fix: switch the query to use Unpivot instead of Pivot, and do the cross-tab layout in the Matrix visual. Or add an explicit filter before the pivot step to limit to a known set of values.

Error: Unpivot creates null rows

When source data has blank cells (a product with no sales in February, for example), Unpivot creates rows with null in the Value column. Fix: after the Unpivot step, add a filter step to remove nulls: Home → Remove Rows → Remove Blank Rows. Or keep the nulls if you want to show zeros in the Matrix — use IF(ISBLANK([Revenue]), 0, [Revenue]) in your measure.

Error: Pivot Column returns "Value.Error" for non-numeric values

Pivot Column with a Sum aggregation fails when the Values column contains text instead of numbers. Fix: before the Pivot step, change the Values column data type to Decimal Number (click the data type icon → Decimal Number). If the column contains mixed text and numbers, add a step to replace errors: Transform → Replace Errors → 0.

Pivot columns appear in wrong order (alphabetical instead of chronological)

Power Query does not sort pivot column headers — they appear in the order unique values are encountered in the data. Fix: after the Pivot step, add a Reorder Columns step (right-click column headers → Move, or drag). Or pre-sort the source table by the date column before the Pivot step so months are encountered in chronological order.

Unpivot creates too many rows (cartesian explosion)

If your source table has both a wide format and duplicate identifier rows, Unpivot multiplies the rows. Fix: before unpivoting, ensure each combination of identifier columns (Product, Date, etc.) is unique. Use Remove Duplicates on the identifier columns first, or Group By to aggregate before unpivoting.

FAQ

How do I convert rows to columns in Power BI?

Use Power Query's Pivot Column feature: open Power Query Editor → select the column whose values will become column headers → Transform → Pivot Column → select the Values Column → set aggregate function to Sum → click OK. For a dynamic pivot that users can restructure after publishing, Flexa Tables handles this in the visual layer without Power Query.

How do I unpivot columns to rows in Power BI?

Open Power Query Editor → select the columns to unpivot → Transform → Unpivot Columns. Power Query creates Attribute and Value columns. Rename them to match your data (e.g. Month and Revenue). Use "Unpivot Other Columns" instead of "Unpivot Columns" when you want new source columns to be automatically included on future refreshes.

What is the difference between pivot and unpivot in Power BI?

Pivot converts rows into columns — it takes unique values from one column and makes each a new column header. Unpivot does the opposite — it converts multiple columns into two columns (one for the original header names, one for the values). In Power BI, unpivot is far more commonly needed because the data model should be in tall (normalized) format for DAX and the Matrix visual to work correctly.

Can I pivot data in Power BI without Power Query?

Yes — the Matrix visual pivots data at the visual layer without changing the data model. Place your row dimension in Rows, your column dimension in Columns, and your measure in Values. For a dynamic pivot where users can restructure after publishing, Flexa Tables adds drag-and-drop pivot in Power BI Service without DAX or Power Query.

Why does Power BI Pivot Column create extra columns when data refreshes?

Pivot Column creates one column per unique value in the pivoted field. If new values appear on refresh (e.g. a new month), Power Query adds new columns — which can break report visuals. To prevent this, filter the pivoted field to a known set of values before pivoting, or switch to visual-layer pivoting with the Matrix visual or Flexa Tables.

When should I use Unpivot vs keeping data in wide format?

Unpivot (tall format) is almost always correct for Power BI data models. Tall format works correctly with DAX time intelligence, supports any number of periods without model changes, and allows the Matrix visual to generate columns dynamically. Wide format is only acceptable when the column count is permanently fixed and the data will never add new columns.