Unpivot Columns

Power BI · Power Query (Data Transformation)

Theory

Unpivot turns columns into rows. Many spreadsheets are wide: one row per region and one column per month. Power BI works best with long (tidy) data: one row per region per month, with a Month column and a value column. Unpivot does this conversion. Select the columns that identify each row, here Region, and choose Unpivot Other Columns: every other column header becomes a value in a new Attribute column, and each cell becomes a value in a Value column, which you then rename.

For example, a Targets sheet with Region, Jan, Feb, Mar and two rows becomes six rows: North Jan 1000, North Feb 1500 and so on. Unpivot Other Columns is safer than unpivoting the selected month columns, because a new Apr column added next month is picked up automatically. Null cells are skipped by unpivot, so they do not create rows.

Why we use it

Wide tables cannot be filtered by month, joined to a Date table or plotted as a trend. Unpivoting gives one value column you can sum and a Month column you can slice by.

When to use it

Use it whenever headers contain data values such as months, years or product names. The reverse, Pivot Column, is for turning a long attribute back into columns, which is rarely needed for modeling.

How it works

  1. Wide table: one column per month
  2. Select the identifier column (Region)
  3. Transform → Unpivot Other Columns
  4. Each month header becomes a row value
  5. Rename Attribute and Value, set types
  6. Long table ready to sum and slice

Syntax

Select the ID column(s) → Transform → Unpivot Columns → Unpivot Other Columns
Rename Attribute → Month, Value → Target

M:
Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text)

Example

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Targets, {"Region"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",
    {{"Attribute", "Month"}, {"Value", "Target"}})

// Or name them directly:
Table.UnpivotOtherColumns(Targets, {"Region"}, "Month", "Target")

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic