Launch Deal · 50% off 1st month ·

1.6 — Pivot, Unpivot, Transpose and Semi-Structured Data

PL-300 Domain 1 · Skill 1.6

What You Need to Know

Power BI models work best with long, narrow tables: one row per fact, one column per attribute. Many sources (Excel reports especially) arrive wide, with a column per month or per region. These transforms reshape them.

Unpivot

Unpivot turns columns into rows. A table with Product, Jan, Feb, Mar becomes Product, Attribute, Value, which you then rename to Month and Sales.

Command Behavior when a new column (say Apr) appears in the source
Unpivot Other Columns (select Product) Apr is unpivoted too. Use this one for growing sources
Unpivot Columns (select Jan–Mar) The generated M uses Table.UnpivotOtherColumns too, so new columns are included
Unpivot Only Selected Columns Only the named columns are unpivoted. Apr stays as a separate column

Pivot

Pivot turns the values in one column into new columns. Select the column whose values become headers, choose Pivot Column, pick the values column, and choose an aggregation (Sum, Count and so on) or Don't Aggregate when every combination appears once. Pivoting is common for attribute/value tables, such as turning Attribute = Color, Size rows into Color and Size columns.

Transpose

Transpose swaps rows and columns completely. It's for data that's sideways, for example an export where each record is a column. After transposing you usually need Use First Row as Headers.

Semi-structured data (JSON, XML, nested tables)

JSON and XML arrive as Records (one object) and Lists (arrays). Power Query shows them as clickable Record, List and Table values.

  1. A top-level List: choose To Table (List Tools), which gives one column of records.
  2. A column of Records: click the expand icon in the header and pick the fields. Clear Use original column name as prefix for shorter names.
  3. A column of Lists: Expand to New Rows (one row per item) or Extract Values (join them into one text value with a delimiter).
  4. A column of Tables (from a merge, or from the Folder connector): expand the columns you need, or aggregate them.

The Folder connector's Combine Files creates a sample file query, a parameter and a function, then runs that function on every file. Changes you make to the Transform Sample File query apply to every file.

Key Concept: Wide month columns are unpivoted into rows, and nested JSON is expanded into columns or rows. Choose Unpivot Other Columns when the source will gain new columns, so refresh keeps working without edits.

Exam Traps

  • Using Unpivot Only Selected Columns when new month columns will be added each month
  • Transposing when the requirement is really to unpivot (transpose swaps the whole table)
  • Pivoting with an aggregation when the values are text and must be kept as they are (choose Don't Aggregate)
  • Expanding a list column to new rows and then summing a parent-level amount, which duplicates it across the new rows
  • Editing a generated function directly instead of the Transform Sample File query

Check your understanding

Practice Scenario

A budget workbook has the columns Product, Jan, Feb and Mar. A new month column is added every month. You need a table with one row per product per month, and refresh must keep working without edits as months are added. What should you do?

Report issue

Log in to save your progress.

Sources