Excel: Quickly Unpivot Multi-Row Headers

Key insights

• Unpivot with Power Query: Convert wide tables that use multiple header rows into a tidy, long table so PivotTables, charts, and analysis tools can work correctly.
  Power Query (Excel or Power BI) handles the transformation without manual copy‑paste.
• Merge Headers workflow: Load the table, keep the top N header rows, transpose, combine header columns into one row, transpose back, and promote that row to column headers.
  This flattens multi-row headers into a single header row ready for unpivoting.
• Anchor Columns and Unpivot: Keep fixed identifier columns (e.g., Region, Product) as anchors, then use "Unpivot Other Columns" to turn the remaining columns into attribute/value pairs.
  After unpivoting, split the Attribute column by a delimiter and set correct data types.
• Dynamic M techniques: Use M steps (like keeping first rows, transposing, and list operations) to build queries that adapt to any number of header rows or added columns.
  Dynamic M avoids brittle formulas and reduces manual updates when the source layout changes.
• Fast Refresh: Improve performance by removing unnecessary "Changed Type" steps early, minimizing applied steps, or using a separate query for header processing to halve refresh time.
  These tweaks speed up large datasets and make refreshes more reliable.
• Analysis-Ready Output: The result preserves the original data via queries, scales to thousands of rows, and produces clean attribute and value columns ideal for reporting, dashboards, and further transformation.
  Most tasks work with the Power Query GUI; use M code for advanced or fully automated solutions.

Keywords

unpivot multiple header rows Excel, unpivot multi-row headers Excel, Power Query unpivot multiple headers, unpivot multiple header rows faster, transform multi-row headers Excel, flatten multiple header rows Excel, combine header rows Excel unpivot, unpivot multi-level headers Excel