Implementing Custom Pivot and Unpivot Strategies in Power Query M: Dynamic Column Detection, Conditional Transposition, and Multi-Attribute Reshaping Beyond Native UI Transforms

Introduction

Here's a scenario that will feel familiar: you receive a monthly sales report from a regional ERP system. The columns are months — January through December — but only the months that have passed are populated. Next month, there will be one more column. The month after that, one more. The native Unpivot Columns button in Power Query works beautifully the first time you run it. The second time, it silently drops the new column because you hard-coded the column list during the initial setup. Your report is wrong, and you don't notice until someone in Finance asks why Q3 numbers look low.

This is exactly the class of problem that custom pivot and unpivot strategies in M solve. The native UI transforms — Pivot Column, Unpivot Columns, Unpivot Other Columns — are excellent starting points, but they make assumptions: static column names, single-attribute reshaping, and one aggregation function per pivot. Real-world data rarely cooperates with those assumptions. What you actually need is the ability to detect columns programmatically at runtime, unpivot conditionally based on column characteristics, and pivot across multiple attributes simultaneously without losing information fidelity.

By the end of this lesson, you'll have genuine command over M's reshaping primitives. You'll be able to write transforms that adapt to schema drift, handle multi-attribute pivoting where each key maps to more than one value column, and conditionally transpose subsets of a table without touching the rest. You'll also understand why these patterns work at the type-system and evaluation level, so you can adapt them when your data doesn't fit the examples exactly.

What you'll learn:

• How to dynamically detect column names at runtime and use them to drive unpivot and pivot operations
• How to write conditional unpivot logic that reshapes only columns matching a specific pattern or type
• How to implement multi-attribute pivot and unpivot, where each key corresponds to more than one value column
• How to build a custom pivot aggregator that goes beyond List.Sum — including List.Max, conditional counts, and concatenation
• How to structure these transforms for maintainability and performance in production M queries

---

Prerequisites

You should be comfortable with the following before working through this lesson:

• Writing M expressions manually in the Advanced Editor
• Understanding the let...in block and step chaining
• Basic use of Table.Pivot, Table.Unpivot, and Table.UnpivotOtherColumns
• Familiarity with List, Record, and Table functions in M
• Understanding of M's lazy evaluation and how steps are referenced

If you've used the native Pivot and Unpivot buttons and read the generated M, you're in the right place.

---

Understanding What the Native Transforms Actually Generate

Before building custom strategies, you need to see exactly what the UI generates — and more importantly, where it falls short. Run Unpivot Columns on a static selection in the UI and you'll get something like this:

= Table.Unpivot(Source, {"Jan", "Feb", "Mar"}, "Month", "Revenue")

The column list is hard-coded as a literal list. If your source grows a new column, it doesn't appear. If a column is renamed, you get an error. This is the brittleness you're solving.

For pivot, the UI generates:

= Table.Pivot(Source, List.Distinct(Source[Month]), "Month", "Revenue", List.Sum)

This one is actually more adaptive — List.Distinct(Source[Month]) reads the distinct values dynamically. But it still assumes a single value column ("Revenue") and a single aggregator. If you need to pivot multiple attributes simultaneously — say, both Revenue and Units Sold — you're stuck.

Understanding these baselines tells you exactly which levers to pull.

---

Dynamic Column Detection for Unpivot

Pattern 1: Unpivot by Column Name Pattern

The most common production scenario: month columns arrive with names like "Jan 2024", "Feb 2024", "Jan 2025". You want to unpivot everything that matches a date-ish pattern without maintaining a column list manually.

The key insight is that Table.ColumnNames/1 returns the current column list as a plain M list at query evaluation time. You can filter that list with List.Select before passing it to Table.Unpivot.

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesReport.xlsx"), true, true){[Item="Sheet1",Kind="Sheet"]}[Data],
    
    // Detect identifier columns — anything that isn't a month pattern
    AllColumns = Table.ColumnNames(Source),
    
    // Month columns follow the pattern "Mon YYYY" (e.g., "Jan 2024", "Feb 2025")
    MonthColumns = List.Select(
        AllColumns,
        each Text.Length(_) = 8 
            and Text.Contains(_, " ")
            and Value.Is(Value.FromText(Text.End(_, 4)), type number)
    ),
    
    // Everything else is an identifier
    IdentifierColumns = List.Difference(AllColumns, MonthColumns),
    
    // Now unpivot using the dynamically detected list
    Unpivoted = Table.Unpivot(Source, MonthColumns, "Month", "Revenue")
in
    Unpivoted

Walk through what's happening here. List.Select iterates over every column name and applies a predicate. The predicate checks three things: the name is 8 characters long, contains a space, and ends with four digits that parse as a number. This is intentionally narrow — you're not trying to write a universal date parser, you're matching the specific convention your source uses.

Tip: Be as specific as your data allows in your detection predicate. A loose predicate (e.g., "any column name containing a number") will catch columns you don't want to reshape and produce a confusing result. When in doubt, test your MonthColumns list as a standalone step before wiring it into Table.Unpivot.

List.Difference gives you the complement — every column that isn't a month column — without you having to maintain that list either. This is the pattern you want: derive identifiers from the detected value columns, not the other way around.

Pattern 2: Unpivot by Column Type

Sometimes the naming convention isn't reliable, but the data type is. In survey exports, for instance, numeric response columns might appear alongside text metadata columns. You want to unpivot the numeric ones.

let
    Source = Csv.Document(
        File.Contents("C:\Data\SurveyResponses.csv"),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TypeDetected = Table.DetectColumnTypes(Promoted, 200),  // sample 200 rows
    
    // Get the schema as a table of {Name, Type} pairs
    Schema = Table.Schema(TypeDetected),
    
    // Filter to numeric columns only
    NumericColumnNames = List.Select(
        Schema[Name],
        (colName) =>
            let
                colKind = Schema{[Name = colName]}[Kind]
            in
                colKind = "number" or colKind = "Int64.Type"
    ),
    
    IdentifierColumns = List.Difference(Table.ColumnNames(TypeDetected), NumericColumnNames),
    
    Unpivoted = Table.Unpivot(TypeDetected, NumericColumnNames, "Question", "Score")
in
    Unpivoted

Table.Schema returns a table with columns including Name, Kind, TypeName, and others. The Kind field gives you a string representation of the detected type. Filtering on it gives you a type-driven column list without any name pattern dependency.

Warning: Table.DetectColumnTypes requires a full pass over the sample rows and can be expensive on large datasets. If you know the column types in advance — even partially — use Table.TransformColumnTypes on known columns and detect the rest. Also note that column type detection is a heuristic; always verify on representative data.

---

Conditional Transposition: Unpivoting Subsets of Rows

Standard unpivot reshapes all rows of the selected columns. But sometimes you need to unpivot only rows matching a condition and leave others intact, then recombine. This is conditional transposition.

Consider a mixed-format export where some rows are "detail" records with columns Q1, Q2, Q3, Q4 representing quarterly figures, and other rows are "summary" records where those same columns mean something completely different (or are null). You don't want to unpivot the summary rows — they need to stay wide.

let
    Source = Excel.Workbook(File.Contents("C:\Data\FinancialMix.xlsx"), true, true){[Item="Data",Kind="Sheet"]}[Data],
    
    // Split by row type
    DetailRows = Table.SelectRows(Source, each [RowType] = "Detail"),
    SummaryRows = Table.SelectRows(Source, each [RowType] = "Summary"),
    
    // Unpivot only the detail rows
    QuarterColumns = {"Q1", "Q2", "Q3", "Q4"},
    UnpivotedDetail = Table.Unpivot(DetailRows, QuarterColumns, "Quarter", "Amount"),
    
    // Add a placeholder column to summary rows to match the schema
    SummaryWithSchema = Table.AddColumn(
        Table.RemoveColumns(SummaryRows, QuarterColumns),
        "Quarter",
        each null,
        type text
    ),
    SummaryWithAmount = Table.AddColumn(SummaryWithSchema, "Amount", each null, type number),
    
    // Recombine
    Combined = Table.Combine({UnpivotedDetail, SummaryWithAmount})
in
    Combined

The recombination step uses Table.Combine, which aligns columns by name and fills missing columns with null. The summary rows don't get unpivoted; they just get harmonized to the same schema as the unpivoted detail rows.

Tip: When you recombine tables with Table.Combine, column order follows the first table in the list. If column order matters downstream, use Table.ReorderColumns on the result, or ensure your constituent tables have identical column sequences before combining.

---

Multi-Attribute Unpivot: When One Key Maps to Multiple Values

This is where things get genuinely interesting and where most practitioners hit a wall. Suppose your source data looks like this — one row per product, with both Budget and Actual figures for each quarter:

ProductID	ProductName	Q1_Budget	Q1_Actual	Q2_Budget	Q2_Actual	Q3_Budget	Q3_Actual
P001	Widget A	10000	9800	12000	11500	9500	9900
P002	Widget B	8000	8200	7500	7300	9000	8800

You need this:

ProductID	ProductName	Quarter	Budget	Actual
P001	Widget A	Q1	10000	9800
P001	Widget A	Q2	12000	11500

A naïve approach would unpivot all quarter columns into a single Attribute/Value pair, then try to split the attribute name. That works, but it produces an intermediate step with twice as many rows that you then have to pivot back. Let's do this properly with a custom M solution.

let
    Source = Excel.Workbook(File.Contents("C:\Data\BudgetActual.xlsx"), true, true){[Item="Sheet1",Kind="Sheet"]}[Data],

    AllColumns = Table.ColumnNames(Source),
    
    // Detect identifier columns — anything without an underscore
    IdentifierCols = List.Select(AllColumns, each not Text.Contains(_, "_")),
    
    // Detect the distinct quarters from column names
    ValueCols = List.Select(AllColumns, each Text.Contains(_, "_")),
    Quarters = List.Distinct(List.Transform(ValueCols, each Text.BeforeDelimiter(_, "_"))),
    
    // Detect the distinct attributes (Budget, Actual, etc.)
    Attributes = List.Distinct(List.Transform(ValueCols, each Text.AfterDelimiter(_, "_"))),
    
    // For each row and each quarter, build a record
    // This is the core of the multi-attribute unpivot
    Expanded = Table.AddColumn(
        Source,
        "QuarterRecords",
        (row) =>
            List.Transform(
                Quarters,
                (q) =>
                    Record.Combine({
                        // Preserve identifier fields from the current row
                        Record.SelectFields(row, IdentifierCols),
                        // Build the Quarter field
                        [Quarter = q],
                        // Dynamically build attribute fields
                        Record.FromList(
                            List.Transform(Attributes, (attr) => row{[Name = q & "_" & attr]}??  null),
                            Attributes
                        )
                    })
            )
    ),
    
    // The QuarterRecords column contains a list of records — expand it
    Expanded2 = Table.TransformColumns(
        Expanded,
        {"QuarterRecords", each _, type list}
    ),
    
    // Remove original wide columns, keep only identifier + QuarterRecords
    Trimmed = Table.SelectColumns(Expanded, IdentifierCols & {"QuarterRecords"}),
    
    // Expand the list column into rows
    ExpandedRows = Table.ExpandListColumn(Trimmed, "QuarterRecords"),
    
    // Expand the record column into individual columns
    FinalExpanded = Table.ExpandRecordColumn(
        ExpandedRows,
        "QuarterRecords",
        IdentifierCols & {"Quarter"} & Attributes,
        IdentifierCols & {"Quarter"} & Attributes
    ),
    
    // Remove the now-duplicated identifier columns from the expansion
    // (they came from the record, so we already have them)
    Result = Table.SelectColumns(
        FinalExpanded,
        IdentifierCols & {"Quarter"} & Attributes
    )
in
    Result

Let's slow down on the critical piece — the Record.FromList / Record.SelectFields block inside List.Transform:

Record.FromList(
    List.Transform(Attributes, (attr) => row[q & "_" & attr] ?? null),
    Attributes
)

This dynamically builds a record from the attribute list. For each attribute (say, "Budget" and "Actual"), it looks up the value in the current row at the column named q & "_" & attr — for Q1 that becomes "Q1_Budget" and "Q1_Actual". Record.FromList takes a list of values and a list of field names and zips them into a record.

The ?? null operator handles the case where a column might not exist for some quarter — this is your safety net against ragged data.

Warning: The row[columnName] syntax in M uses positional lookup when given an index and field lookup when given a record pattern. To look up a field by a dynamic string name, you use row{[Name = dynamicString]} — but that syntax is for tables. For records, use Record.Field(row, columnName). The ?? null operator after it handles missing field names gracefully.

Correcting the dynamic field lookup for records:

Record.FromList(
    List.Transform(Attributes, (attr) => Record.Field(row, q & "_" & attr) ?? null),
    Attributes
)

This is the production-safe version.

---

Custom Pivot Aggregation Beyond List.Sum

The native Table.Pivot allows one aggregation function. In practice, you often need something more nuanced: concatenate values as a comma-separated list, count non-blank values, or apply conditional logic during aggregation.

Building a Pivot with Table.Group + Record Construction

The most flexible approach bypasses Table.Pivot entirely and uses Table.Group with a custom aggregation:

let
    Source = Csv.Document(
        File.Contents("C:\Data\TicketLog.csv"),
        [Delimiter=",", Encoding=65001]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    // Source has: TicketID, Region, Category, Priority, ResolvedDate, SLAMet (Y/N)
    // Goal: one row per Region+Category, columns for each Priority level
    // Aggregation: count of tickets, but ONLY where SLAMet = "Y"
    
    Priorities = List.Distinct(Promoted[Priority]),
    
    Pivoted = Table.Group(
        Promoted,
        {"Region", "Category"},
        {
            "PivotData",
            (groupTable) =>
                Record.FromList(
                    List.Transform(
                        Priorities,
                        (p) =>
                            List.Count(
                                List.Select(
                                    Table.SelectRows(groupTable, each [Priority] = p)[SLAMet],
                                    each _ = "Y"
                                )
                            )
                    ),
                    Priorities
                ),
            type record
        }
    ),
    
    // Expand the PivotData record into columns
    Expanded = Table.ExpandRecordColumn(Pivoted, "PivotData", Priorities, Priorities)
in
    Expanded

This approach gives you total control over aggregation. The Table.Group step produces one row per Region+Category combination. The aggregation function receives the sub-table for each group and returns a record. You build that record by iterating over the distinct Priority values, applying any logic you want inside List.Count/List.Select.

Tip: The third element of the aggregation specification (type record) tells M what return type to expect. Getting this right helps M's engine optimize evaluation and prevents type errors when you expand the record column downstream.

Concatenation Aggregation

For a text-concatenation pivot — say, all ticket IDs for each priority as a comma-separated string:

(p) =>
    Text.Combine(
        Table.SelectRows(groupTable, each [Priority] = p)[TicketID],
        ", "
    )

Swap this lambda in place of the List.Count block and you have a text-pivot aggregator. The underlying pattern is identical; only the aggregation logic changes.

---

Dynamic Pivot with Variable Column Counts

A final production pattern: fully dynamic pivot where both the group keys and pivot column names are determined at runtime. This handles the case where you don't know what category values will appear in a given month's data.

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesData.xlsx"), true, true){[Item="Transactions",Kind="Sheet"]}[Data],
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    // Detect pivot column values dynamically
    PivotValues = List.Sort(List.Distinct(Promoted[SalesChannel])),
    
    // Group and pivot
    Grouped = Table.Group(
        Promoted,
        {"Region", "ProductLine"},
        {
            "ChannelRevenue",
            (grp) =>
                Record.FromList(
                    List.Transform(
                        PivotValues,
                        (ch) =>
                            List.Sum(
                                Table.SelectRows(grp, each [SalesChannel] = ch)[Revenue]
                            )
                    ),
                    PivotValues
                ),
            type record
        }
    ),
    
    Result = Table.ExpandRecordColumn(Grouped, "ChannelRevenue", PivotValues, PivotValues)
in
    Result

Notice List.Sort on PivotValues. The native Table.Pivot uses List.Distinct order which is insertion order — unpredictable and different each run depending on source data. Sorting your pivot columns explicitly gives you deterministic output.

---

Hands-On Exercise

Build a complete multi-attribute unpivot and re-pivot pipeline using the following scenario:

Scenario: Your company tracks employee training completion. The source file has this structure:

EmployeeID	Department	Manager	Safety_Completed	Safety_Score	Compliance_Completed	Compliance_Score	Leadership_Completed	Leadership_Score

Where Completed is a date (or null if not done) and Score is numeric (or null).

Your task:

1. Write M code that dynamically detects the training module names ("Safety", "Compliance", "Leadership") from the column headers at runtime.
2. Unpivot the data so each row represents one employee + one training module, with separate columns for Completed and Score.
3. Add a calculated column Passed that is true when Score >= 70 and Completed is not null.
4. Re-pivot the result to produce a summary by Department: one row per Department, columns for each training module showing the count of employees who Passed.

Hints:

• Use Text.BeforeDelimiter and Text.AfterDelimiter to split column names on "_"
• Use the multi-attribute unpivot pattern from the lesson for step 2
• Use the Table.Group + Record.FromList pivot pattern for step 4
• The identifier columns are EmployeeID, Department, and Manager

Work through this without referring to the lesson code first. When you get stuck, look at the relevant section — but understand why the pattern applies before copying it.

---

Common Mistakes & Troubleshooting

Mistake 1: Using `Table.Unpivot` with a Hard-Coded List Inside a Dynamic Query

Symptom: New columns from the source are silently ignored; the output has fewer rows than expected.

Fix: Always derive your column list from Table.ColumnNames(Source) at the step immediately preceding your unpivot. Never paste a literal list; always compute it.

Mistake 2: `Record.Field` vs. `row[FieldName]` Syntax

Symptom: Formula errors when trying to do dynamic field lookup on a row record.

Explanation: Inside a Table.AddColumn or Table.Group context, each binds to a record representing the current row. You can use _[FieldName] for static field names. For dynamic names — where the field name is computed from a variable — you must use Record.Field(_, dynamicName).

// WRONG — only works if "FieldName" is a literal
each _[someVariable]  

// RIGHT — works with a computed field name
each Record.Field(_, someVariable)

Mistake 3: Column Order Mismatch After `Table.Combine`

Symptom: Columns appear in unexpected order after combining detail and summary tables.

Fix: Use Table.SelectColumns with an explicit ordered list on each constituent table before Table.Combine, or use Table.ReorderColumns on the combined result.

Mistake 4: Forgetting to Sort Pivot Column Values

Symptom: Report column order changes month to month because new values appear in different insertion positions.

Fix: Always wrap your List.Distinct with List.Sort — or better, List.Sort(..., Order.Ascending) — before using it as pivot column names.

Mistake 5: Expanding Too Many Columns from a Record

Symptom: After Table.ExpandRecordColumn, you have duplicate identifier columns — once from the original row, once from the expanded record.

Fix: When you build records inside Table.Group or Table.AddColumn, only include in the record the fields you intend to expand as new columns. Identifier fields should stay as regular table columns, not be embedded in the record.

Troubleshooting: Slow Multi-Attribute Unpivot on Large Tables

If your multi-attribute unpivot using Table.AddColumn + List.Transform is slow, the culprit is usually the nested Record.Field calls being evaluated for every row × attribute combination without caching.

Fix: Add a Table.Buffer call on your source before the transform:

Buffered = Table.Buffer(Source),

This materializes the table in memory and prevents repeated source evaluations during the row-by-row transform. Use this judiciously — it increases memory consumption — but it's the correct tool when you have nested iterations over the same source table.

---

Summary & Next Steps

Here's what you've built competence in during this lesson:

Dynamic column detection using Table.ColumnNames, List.Select, and Table.Schema — so your reshaping logic survives schema drift without human intervention.

Conditional transposition by splitting a table, transforming the target subset, harmonizing schemas, and recombining with Table.Combine — giving you surgical control over which rows get reshaped.

Multi-attribute unpivot using Table.AddColumn + List.Transform + Record.FromList + Record.Field — the production pattern for collapsing two or more value columns per key into a single tidy row.

Custom pivot aggregation using Table.Group + Record.FromList + any aggregation logic you need — going beyond the single-function limitation of native Table.Pivot.

Deterministic pivot output by sorting distinct values before using them as column names.

The common thread across all these patterns is building on M's native list and record primitives rather than fighting against them. Once you internalize that a row is a record, a column is a list, and a table is a list of records, the reshaping operations become compositions of smaller, understandable transformations.

Where to go next:

• Study Table.TransformRows and when it outperforms Table.AddColumn chains — particularly for multi-column transformations applied in a single pass
• Explore recursive M functions for hierarchical unpivot — when your data is nested JSON or XML and requires structural reshaping rather than tabular reshaping
• Investigate Value.ReplaceType for adding precise type metadata to your reshaped tables, which becomes critical when your M output feeds into DirectQuery or composite models in Power BI
• Look into Table.Partition for splitting large tables before applying expensive custom transforms, then recombining — a parallel-safe performance pattern

The techniques in this lesson are the foundation for building M functions you can deploy as reusable components across multiple queries. Wrapping the multi-attribute unpivot pattern in a function that accepts a table, identifier column list, and delimiter is a valuable next exercise — it's the path from practitioner to M library author.