Unpivoting, Aggregating, and Reshaping Lakehouse Data in Dataflow Gen2: Advanced Power Query Transformations Before Writing to Delta Tables

Introduction

Picture this: your source system is a legacy ERP that exports monthly financial data as a wide spreadsheet, with one column per month — Jan_2024, Feb_2024, Mar_2024, and so on across twelve columns. Your Power BI report needs that data in a proper time-series format, with one row per month per account, so you can write DAX measures that actually work. Or maybe you're pulling survey results where each question is its own column, but your fact table schema demands a narrow, tall structure. Or you have a denormalized product catalog where category attributes are scattered across positional columns that shift with each export.

These are the unglamorous realities of data engineering work. The data arrives in the shape that was convenient for whoever built the source system — not the shape your lakehouse needs. And while you could land the raw shape into a bronze Delta table and reshape it later with PySpark, there's a compelling case for doing the heavy reshaping work in Dataflow Gen2's Power Query engine before the data ever touches OneLake. You get an interactive, visual, low-code environment with immediate data previews, and the M language underneath is genuinely capable of sophisticated transformations that most practitioners never explore past the basics.

This lesson goes deep on three families of advanced transformation — unpivoting, aggregating, and structural reshaping — and shows you how to sequence them correctly, avoid the traps that corrupt your Delta table schemas, and write clean, maintainable M code when the visual interface runs out of road. By the end, you'll be able to take an arbitrary wide or denormalized source shape and produce a clean, typed, partitioned-friendly Delta table output, entirely within Dataflow Gen2.

What you'll learn:

• How to correctly apply column-level and value-level unpivot strategies, including the often-misunderstood "Unpivot Other Columns" pattern
• How to perform grouped aggregations in Power Query M and why the order of operations relative to unpivoting matters enormously
• How to reshape data through pivoting, transposing, and custom column logic to meet specific Delta table schema requirements
• How to inspect and repair the M code that Power Query generates, including fixing type inference errors that cause Delta writes to fail silently
• How to choose between doing reshaping work in Dataflow Gen2 versus handing it off to a Spark notebook, and when the answer is genuinely not obvious

---

Prerequisites

This lesson assumes you're working within an active Microsoft Fabric workspace with at least an F2 capacity or a Fabric trial — if you need to set that up, see Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace. You should have a lakehouse already created and understand how it relates to Delta tables in OneLake — Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint covers that foundation. You should also be comfortable with the Dataflow Gen2 basics covered in Ingesting Data with Dataflow Gen2: Power Query Skills in Microsoft Fabric — this lesson builds directly on top of that baseline.

Some exposure to M formula language syntax will help, but isn't strictly required. We'll explain each piece of M we write.

---

Understanding the Shape Problem: Why Reshaping Happens in the Middle, Not the End

Before we touch a single transformation, it's worth being precise about why reshaping matters for Delta tables specifically. Delta tables have fixed schemas. Every column has a declared name and data type, stored in the _delta_log as part of the table's metadata. When Dataflow Gen2 writes to a Delta table destination, it reads the output schema of your Power Query query and creates or reconciles a Delta schema from it.

This has two important consequences that bite people constantly.

First, if your Power Query output schema is wide and variable — for example, if the number of columns changes depending on what months exist in the source data — then every refresh can produce a different schema, and Delta will either throw a schema mismatch error or (if you've enabled schema evolution) silently add new columns that break your downstream semantic model. Landing data in the raw wide shape first means you inherit this problem everywhere downstream.

Second, the column data types that Power Query infers are not always what Delta wants. Power Query will often infer any type for columns that came out of unpivot operations, because the unpivot merged columns of potentially different types into a single value column. Delta doesn't accept any — it needs a concrete type. Failing to fix this before writing produces a Delta table with string columns where you wanted decimal, or worse, a write failure with a cryptic error.

This is the mental model you need to carry through everything that follows. Reshaping isn't decoration. It's schema engineering.

---

Unpivoting: Rotating Wide Data Into Tall, Analysis-Ready Tables

The Basic Mechanics

Unpivoting converts columns into rows. In Power Query, you select one or more columns that represent attribute headers — like month names, product names, or question numbers — and transform them into two new columns: one holding the former column name (the attribute), and one holding the value that was in that cell.

Let's work with a concrete scenario. You're ingesting a GL account balance export that looks like this:

AccountCode	AccountName	Jan_2024	Feb_2024	Mar_2024	Apr_2024
4010	Revenue - Product	125000.50	131000.00	98000.75	142000.00
5010	COGS - Product	62000.00	65000.00	49000.00	71000.00
6010	Salaries	85000.00	85000.00	85000.00	87000.00

You want this shape for your silver layer:

AccountCode	AccountName	PeriodKey	Amount
4010	Revenue - Product	Jan_2024	125000.50
4010	Revenue - Product	Feb_2024	131000.00

The Three Unpivot Variants — and When Each One Breaks

Power Query offers three flavors of unpivot, and choosing the wrong one is the single most common mistake at this stage.

Unpivot Columns operates on an explicit list of columns you select. The M it generates looks like:

= Table.Unpivot(
    #"Previous Step",
    {"Jan_2024", "Feb_2024", "Mar_2024", "Apr_2024"},
    "PeriodKey",
    "Amount"
)

The problem: if a new month appears in the source next month (May_2024), it won't be in that explicit list. Power Query will quietly drop it. You won't get an error. You'll just lose data. This is an especially nasty failure mode because it's invisible.

Unpivot Other Columns is the correct choice for most variable-width scenarios. You select the columns you want to keep as identifier columns (AccountCode, AccountName), then tell Power Query to unpivot everything else. The generated M is:

= Table.UnpivotOtherColumns(
    #"Previous Step",
    {"AccountCode", "AccountName"},
    "PeriodKey",
    "Amount"
)

Now when May_2024 appears in the source, it gets unpivoted automatically. Your identifier list is stable; the attribute columns are dynamic.

Unpivot Only Selected Columns is the same as the explicit variant but with a slightly different UI path. Use it only when you genuinely want a fixed, known set of columns to rotate.

Here's that defensive pattern in practice:

// Step 1: Lock down the columns you'll work with
#"Selected Columns" = Table.SelectColumns(
    Source,
    {"AccountCode", "AccountName", "Jan_2024", "Feb_2024", "Mar_2024", "Apr_2024"}
),

// Actually — for dynamic month columns, use a different approach:
// Step 1: Identify all columns
AllColumns = Table.ColumnNames(Source),
IdentifierColumns = {"AccountCode", "AccountName"},
MonthColumns = List.Difference(AllColumns, IdentifierColumns),

// Step 2: Unpivot only confirmed month columns by pattern
FilteredMonthColumns = List.Select(MonthColumns, each Text.Contains(_, "_202")),

#"Unpivoted" = Table.Unpivot(Source, FilteredMonthColumns, "PeriodKey", "Amount")

This pattern — using List.Select with a pattern match to identify the attribute columns, then passing that dynamic list to Table.Unpivot — gives you both the safety of explicit column selection and the flexibility to handle new periods automatically.

Fixing Type Inference After Unpivot

After an unpivot, the Amount column will almost certainly be typed as any or text, because Power Query merged what might have been differently-typed source columns. You must fix this explicitly:

#"Fixed Types" = Table.TransformColumnTypes(
    #"Unpivoted",
    {
        {"AccountCode", type text},
        {"AccountName", type text},
        {"PeriodKey", type text},
        {"Amount", type number}
    }
)

---

Parsing PeriodKey: Turning Column Names Into Proper Date Values

A column named PeriodKey containing the string "Jan_2024" is marginally useful, but a proper date or integer period key is far more useful for filtering, sorting, and joining in your Gold layer. You can parse those strings in M without leaving Dataflow Gen2.

// Add a proper period date (first day of month)
#"Added PeriodDate" = Table.AddColumn(
    #"Fixed Types",
    "PeriodDate",
    each
        let
            parts = Text.Split([PeriodKey], "_"),
            monthName = parts{0},
            year = Number.FromText(parts{1}),
            monthNumber = Date.Month(Date.FromText("1 " & monthName & " " & Text.From(year)))
        in
            #date(year, monthNumber, 1),
    type date
),

// Also add an integer period key for efficient Delta partitioning
#"Added PeriodInt" = Table.AddColumn(
    #"Added PeriodDate",
    "PeriodYearMonth",
    each Date.Year([PeriodDate]) * 100 + Date.Month([PeriodDate]),
    type number
)

Now you have PeriodDate as a proper date type and PeriodYearMonth as an integer like 202401, which is a natural partition key for your Delta table. This matters because optimizing Delta table performance often depends on having a clean, low-cardinality integer or date column to partition on.

---

Aggregating in Power Query: When and How to Group Before Writing

Why Aggregate in Dataflow Gen2 at All?

There's a legitimate debate here. PySpark is a far more powerful aggregation engine than Power Query's in-process M interpreter. If you're aggregating millions of rows across complex grouping keys, you should probably be doing that in a Spark notebook. But there's a meaningful class of problems where aggregating in Dataflow Gen2 is the right call:

• You're already doing the unpivot in Dataflow Gen2, and the aggregation is a simple group-by that reduces row count significantly before writing
• The raw source is tens or hundreds of thousands of rows, not millions
• You want the aggregation logic to be visible and auditable in Power Query's step-by-step UI, not buried in a notebook
• You're building the Silver layer in a medallion architecture and the aggregation is part of the standardization logic, not the analytical layer

The `Table.Group` Function: Beyond the UI

The Power Query UI's "Group By" dialog generates Table.Group calls, but it only exposes a subset of what the function can do. Learning to write Table.Group directly unlocks the full feature set.

The signature is:

Table.Group(
    table as table,
    key as any,              // column name(s) to group by
    aggregatedColumns as list, // list of {name, aggregation function} pairs
    groupKind as nullable GroupKind.Type,  // GroupKind.Global (default) or GroupKind.Local
    comparer as nullable function
)

Here's a realistic example. After unpivoting, you want to aggregate your GL balances to the account/period level, with a count of source rows (for data quality) and a sum of amount:

#"Aggregated" = Table.Group(
    #"Added PeriodInt",
    {"AccountCode", "AccountName", "PeriodYearMonth", "PeriodDate"},
    {
        {"TotalAmount", each List.Sum([Amount]), type number},
        {"RowCount", each Table.RowCount(_), Int64.Type},
        {"MaxAmount", each List.Max([Amount]), type number},
        {"MinAmount", each List.Min([Amount]), type number}
    }
)

Notice we're grouping by PeriodDate along with PeriodYearMonth — even though they're redundant, including both means they appear in the output table without needing an extra join step. This is a common power-user pattern.

Multi-Column Aggregations With Custom Logic

One of the most powerful patterns in Table.Group is using a sub-table function — the each clause receives an entire sub-table for each group, which means you can run arbitrary table operations per group:

#"Aggregated With Stats" = Table.Group(
    #"Fixed Types",
    {"AccountCode", "PeriodYearMonth"},
    {
        {
            "AmountStats",
            each
                let
                    amounts = [Amount],
                    total = List.Sum(amounts),
                    avg = List.Average(amounts),
                    variance = List.Average(List.Transform(amounts, each (_ - avg)^2))
                in
                    Record.FromList({total, avg, Number.Sqrt(variance)}, {"Total", "Average", "StdDev"}),
            type record
        }
    }
)

This generates a column of record values, which you'd then expand with Table.ExpandRecordColumn. This two-step pattern — aggregate into records, then expand — is cleaner than writing multiple aggregation entries when the computations share intermediate values.

#"Expanded Stats" = Table.ExpandRecordColumn(
    #"Aggregated With Stats",
    "AmountStats",
    {"Total", "Average", "StdDev"},
    {"TotalAmount", "AvgAmount", "StdDevAmount"}
)

---

Reshaping: Pivoting, Transposing, and Structural Transformations

When You Need to Pivot After Unpivoting

It sounds counterintuitive, but a common pattern is to unpivot first (to normalize the data), perform some transformation or lookup, and then re-pivot to a specific wide shape for a Gold layer fact table. This is useful when your reporting layer needs a specific set of columns with guaranteed names — for example, when you're feeding a Direct Lake semantic model where the measure definitions reference specific column names.

The M function for pivoting is Table.Pivot:

Table.Pivot(
    table as table,
    pivotValues as list,  // the distinct values that become columns
    attributeColumn as text,  // the column whose values become headers
    valueColumn as text,  // the column whose values fill the cells
    aggregationFunction as nullable function  // what to do when there are multiple values per cell
)

Here's a concrete example. You have a normalized metrics table with columns MetricName and MetricValue, and you want to pivot specific metrics into columns for a KPI summary table:

// First, get the distinct metric names you want as columns
// (hardcode them for stability — see the Warning below)
let
    TargetMetrics = {"Revenue", "COGS", "GrossProfit", "OperatingExpenses"},

    #"Pivoted Metrics" = Table.Pivot(
        #"Filtered Metrics",
        TargetMetrics,
        "MetricName",
        "MetricValue",
        List.Sum  // aggregate function handles duplicate metric/period combinations
    ),

    #"Fixed Pivot Types" = Table.TransformColumnTypes(
        #"Pivoted Metrics",
        List.Transform(TargetMetrics, each {_, type number})
    )
in
    #"Fixed Pivot Types"

Transposing Tables

Table.Transpose flips rows and columns. This is a niche operation, but it's essential when your source delivers data in a "properties as rows" format and you need "properties as columns."

Imagine a configuration table that arrives like this:

ParameterName	ParameterValue
StartDate	2024-01-01
EndDate	2024-12-31
Currency	USD
Region	EMEA

You want a single-row reference table for joining:

let
    Source = /* your source connection */,

    // Promote the ParameterName column to headers
    #"Transposed" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed", [PromoteAllScalars = true]),
    #"Fixed Types" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {
            {"StartDate", type date},
            {"EndDate", type date},
            {"Currency", type text},
            {"Region", type text}
        }
    )
in
    #"Fixed Types"

The critical detail here: Table.Transpose doesn't preserve column headers — after the transpose, you get generic column names (Column1, Column2, etc.), and the original first column becomes the first row. You almost always need Table.PromoteHeaders immediately after.

Building Computed Columns That Span Reshaping Boundaries

One of the trickier situations is when you need a computed column whose values depend on the result of an unpivot or aggregation. For example, after aggregating to period/account totals, you might want to compute a rolling 3-month average for each account. Power Query doesn't have a native window function, but you can simulate it with a self-join pattern using Table.AddColumn and a reference to the accumulated result:

let
    // ... previous steps produce #"Aggregated" with columns: AccountCode, PeriodYearMonth, TotalAmount

    // Add rolling 3-month average using a nested group reference
    #"With Rolling Avg" = Table.AddColumn(
        #"Aggregated",
        "Rolling3MAmt",
        each
            let
                currentPeriod = [PeriodYearMonth],
                currentAccount = [AccountCode],
                // Calculate prior periods: current and 2 before
                // PeriodYearMonth is YYYYMM integer
                currentYear = Number.IntegerDivide(currentPeriod, 100),
                currentMonth = Number.Mod(currentPeriod, 100),
                // Build list of 3 periods to average
                periods = List.Generate(
                    () => [y = currentYear, m = currentMonth, count = 0],
                    each [count] < 3,
                    each [
                        y = if [m] = 1 then [y] - 1 else [y],
                        m = if [m] = 1 then 12 else [m] - 1,
                        count = [count] + 1
                    ],
                    each [y] * 100 + [m]
                ),
                // Filter the aggregated table to matching rows
                matchingRows = Table.SelectRows(
                    #"Aggregated",
                    each [AccountCode] = currentAccount and List.Contains(periods, [PeriodYearMonth])
                ),
                avg = List.Average(Table.Column(matchingRows, "TotalAmount"))
            in
                avg,
        type number
    )
in
    #"With Rolling Avg"

---

Working in the Advanced Editor: Structuring Complex M Queries

When your transformations get sophisticated enough, the graphical step list becomes a liability. Steps have auto-generated names like #"Unpivoted Columns1" and the dependency chain is invisible. The Advanced Editor is where you take full control.

Here's how a well-structured complex query looks in M — using the GL balance scenario end-to-end:

let
    // =========================================
    // SOURCE LAYER
    // =========================================
    Source = Lakehouse.Contents(null){[workspaceId = "your-workspace-id", lakehouseId = "your-lakehouse-id"]}[Data],
    GLBalances_Raw = Source{[Schema = "dbo", Item = "gl_balances_raw"]}[Data],

    // =========================================
    // SCHEMA HARDENING: select and validate
    // =========================================
    AllColumns = Table.ColumnNames(GLBalances_Raw),
    IdentifierCols = {"AccountCode", "AccountName", "AccountType"},
    PeriodCols = List.Select(
        List.Difference(AllColumns, IdentifierCols),
        each Text.StartsWith(_, "Jan_") or Text.StartsWith(_, "Feb_") or
             Text.StartsWith(_, "Mar_") or Text.StartsWith(_, "Apr_") or
             Text.StartsWith(_, "May_") or Text.StartsWith(_, "Jun_") or
             Text.StartsWith(_, "Jul_") or Text.StartsWith(_, "Aug_") or
             Text.StartsWith(_, "Sep_") or Text.StartsWith(_, "Oct_") or
             Text.StartsWith(_, "Nov_") or Text.StartsWith(_, "Dec_")
    ),

    // =========================================
    // UNPIVOT
    // =========================================
    Unpivoted = Table.Unpivot(GLBalances_Raw, PeriodCols, "PeriodKey", "RawAmount"),

    // =========================================
    // TYPE FIXES AND PARSING
    // =========================================
    WithNumericAmount = Table.TransformColumnTypes(
        Unpivoted,
        {{"RawAmount", type number}}
    ),

    MonthMap = [
        Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun = 6,
        Jul = 7, Aug = 8, Sep = 9, Oct = 10, Nov = 11, Dec = 12
    ],

    WithPeriodDate = Table.AddColumn(
        WithNumericAmount,
        "PeriodDate",
        each
            let
                parts = Text.Split([PeriodKey], "_"),
                abbr = parts{0},
                yr = Number.FromText(parts{1}),
                mo = Record.Field(MonthMap, abbr)
            in
                #date(yr, mo, 1),
        type date
    ),

    WithPeriodInt = Table.AddColumn(
        WithPeriodDate,
        "PeriodYearMonth",
        each Date.Year([PeriodDate]) * 100 + Date.Month([PeriodDate]),
        Int64.Type
    ),

    // =========================================
    // NULL HANDLING
    // =========================================
    WithNullsReplaced = Table.ReplaceValue(
        WithPeriodInt,
        null,
        0.0,
        Replacer.ReplaceValue,
        {"RawAmount"}
    ),

    // =========================================
    // AGGREGATION
    // =========================================
    Aggregated = Table.Group(
        WithNullsReplaced,
        {"AccountCode", "AccountName", "AccountType", "PeriodYearMonth", "PeriodDate"},
        {
            {"TotalAmount", each List.Sum([RawAmount]), type number},
            {"SourceRowCount", each Table.RowCount(_), Int64.Type}
        }
    ),

    // =========================================
    // FINAL OUTPUT SCHEMA — lock every column
    // =========================================
    FinalOutput = Table.SelectColumns(
        Aggregated,
        {"AccountCode", "AccountName", "AccountType", "PeriodYearMonth", "PeriodDate", "TotalAmount", "SourceRowCount"}
    ),

    TypedOutput = Table.TransformColumnTypes(
        FinalOutput,
        {
            {"AccountCode", type text},
            {"AccountName", type text},
            {"AccountType", type text},
            {"PeriodYearMonth", Int64.Type},
            {"PeriodDate", type date},
            {"TotalAmount", type number},
            {"SourceRowCount", Int64.Type}
        }
    )
in
    TypedOutput

Notice the structural pattern: source, schema hardening, unpivot, type fixing, parsing, null handling, aggregation, final schema lock. This sequence is deliberately ordered to prevent type errors cascading through the query.

---

Configuring the Delta Table Destination

Once your query output is clean and typed, you connect it to a lakehouse Delta table destination. In Dataflow Gen2, you do this by clicking the "+" next to "Data destination" in the query's panel, selecting your lakehouse, and specifying the table name.

The two most important settings in the destination configuration dialog are:

Update method: Choose "Replace" for full refreshes where you want the Delta table to reflect exactly what Power Query produces. Choose "Append" if you're doing incremental loads — but be very careful, because Dataflow Gen2 in Append mode doesn't deduplicate; every refresh appends all rows. For proper incremental patterns with watermarks, you're better served by a pipeline with watermark lookup activities than by Dataflow Gen2 alone.

Schema: Dataflow Gen2 will show you the inferred schema. If any column shows Any type here, your earlier type-fixing steps didn't work — go back and fix them. A column typed as Any in the destination dialog will be written as string to Delta, which will silently corrupt numeric data.

---

Advanced Pattern: Multi-Query Staging Within a Single Dataflow

Dataflow Gen2 supports multiple queries within a single dataflow, and those queries can reference each other. This is the Power Query equivalent of a CTE chain — you can build a staging query that does the unpivot, then a second query that aggregates it, and a third that applies final business logic and writes to Delta.

The benefit is modularity: you can disable the destination on intermediate queries (so they don't write to Delta), use them purely as computation steps, and only materialize the final result. This also makes debugging easier — you can enable and disable individual query outputs to see intermediate state.

Here's how to set this up:

1. Create your base unpivot query. Right-click it in the Queries panel and disable "Enable staging" if you don't want it to write anywhere — it becomes a pure in-memory transformation.
2. Create a second query. In its source step, instead of connecting to the lakehouse, use = #"Your First Query Name" to reference the first query directly.
3. Apply aggregations in the second query.
4. Add the Delta table destination only to the final query.

If you're writing the final output to multiple destinations — for example, one table for the lakehouse and one for a warehouse — you'll want the pattern described in Branching Dataflow Gen2 Outputs to Multiple Destinations.

---

Choosing Between Dataflow Gen2 and Spark: An Honest Comparison

This is the question that every experienced Fabric practitioner eventually has to answer deliberately rather than by default.

Use Dataflow Gen2 for reshaping when:

• Your data volume after transformation is under ~5 million rows
• The source data arrives from Power Query-native connectors (SharePoint, web APIs, OData, Excel files)
• Your team has Power Query/M skills and limited PySpark exposure
• The transformations are primarily structural (unpivot, pivot, rename, cast) rather than compute-intensive (complex window functions, large joins, ML feature engineering)
• You need a no-code/low-code audit trail visible in a GUI

Use Spark notebooks for reshaping when:

• Your source data exceeds 10 million rows, especially before aggregation
• You need true window functions (LAG, LEAD, RANK, running totals across partitions)
• The reshaping involves joining multiple large tables
• You need MERGE/upsert semantics (Dataflow Gen2 doesn't support true MERGE to Delta)
• You're already orchestrating the load in a pipeline and the transformation is just another notebook activity

For everything in between, the honest answer is: use whichever one your team can maintain. A Dataflow Gen2 query that works reliably beats a Spark notebook that only one person on the team understands how to debug.

The medallion architecture gives you natural seams to split this work: Dataflow Gen2 for Bronze-to-Silver (structural normalization, type fixing, unpivoting), Spark notebooks for Silver-to-Gold (joins, window functions, complex aggregations).

---

Hands-On Exercise

This exercise builds a complete end-to-end transformation pipeline using the patterns from this lesson. You'll need a Fabric workspace with a lakehouse.

Setup: Create the Source Data

First, you need a wide-format source table in your lakehouse. You can create it by running this PySpark snippet in a notebook:

from pyspark.sql import Row
from pyspark.sql.types import StructType, StructField, StringType, DoubleType

data = [
    Row(AccountCode="4010", AccountName="Revenue - Product A", AccountType="Revenue",
        Jan_2024=125000.50, Feb_2024=131000.00, Mar_2024=98000.75,
        Apr_2024=142000.00, May_2024=138500.25, Jun_2024=None),
    Row(AccountCode="4020", AccountName="Revenue - Product B", AccountType="Revenue",
        Jan_2024=67000.00, Feb_2024=71000.00, Mar_2024=59000.00,
        Apr_2024=82000.00, May_2024=None, Jun_2024=88000.00),
    Row(AccountCode="5010", AccountName="COGS - Product A", AccountType="Expense",
        Jan_2024=62000.00, Feb_2024=65000.00, Mar_2024=49000.00,
        Apr_2024=71000.00, May_2024=69000.00, Jun_2024=None),
    Row(AccountCode="6010", AccountName="Salaries", AccountType="Expense",
        Jan_2024=85000.00, Feb_2024=85000.00, Mar_2024=85000.00,
        Apr_2024=87000.00, May_2024=87000.00, Jun_2024=87000.00),
]

schema = StructType([
    StructField("AccountCode", StringType()),
    StructField("AccountName", StringType()),
    StructField("AccountType", StringType()),
    StructField("Jan_2024", DoubleType()),
    StructField("Feb_2024", DoubleType()),
    StructField("Mar_2024", DoubleType()),
    StructField("Apr_2024", DoubleType()),
    StructField("May_2024", DoubleType()),
    StructField("Jun_2024", DoubleType()),
])

df = spark.createDataFrame(data, schema)
df.write.format("delta").mode("overwrite").saveAsTable("gl_balances_raw")

The Exercise

1. Create a new Dataflow Gen2 in your workspace. Connect to your lakehouse as a source and select the gl_balances_raw table.

2. Apply the dynamic period column identification pattern from earlier in this lesson. Verify in the Applied Steps panel that PeriodCols correctly identifies the six month columns.

3. Unpivot using Table.UnpivotOtherColumns with AccountCode, AccountName, and AccountType as identifiers. Confirm you get 24 rows (4 accounts × 6 months).

4. Fix the RawAmount type to number. Check that the 3 null values (May_2024 for Product B, Jun_2024 for Product A, Jun_2024 for COGS) are visible as null in the preview.

5. Replace nulls in RawAmount with 0. Verify all 24 rows now have a numeric amount.

6. Parse the PeriodKey column to add PeriodDate (type date) and PeriodYearMonth (type Int64) columns using the month abbreviation map pattern.

7. Aggregate using Table.Group to sum TotalAmount and count SourceRowCount per AccountCode/AccountType/PeriodYearMonth.

8. Apply a final type lock with an explicit Table.TransformColumnTypes.

9. Add a Delta table destination pointing to a new table called gl_balances_silver. In the destination dialog, verify no columns show Any type.

10. Publish and refresh the dataflow. Then open the SQL Analytics Endpoint of your lakehouse and run:

    SELECT AccountType, PeriodYearMonth, SUM(TotalAmount) as TotalByType
    FROM gl_balances_silver
    GROUP BY AccountType, PeriodYearMonth
    ORDER BY AccountType, PeriodYearMonth
    

    Verify the results match your expectations: two expense accounts and two revenue accounts, correctly summed by period.

---

Common Mistakes & Troubleshooting

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

This error happens when a step references a column by name that no longer exists. The most common cause: you added an explicit Table.UnpivotOtherColumns step, then reordered steps so that a column rename happens after the unpivot references the old name. Fix by reordering steps in the Applied Steps panel, or editing the M directly.

Delta write succeeds but all numeric columns contain null

This almost always means your Amount or similar numeric column was typed as text somewhere in the M chain, and the Delta write silently converted non-parseable strings to null. Open the Advanced Editor, search for type text and type any, and trace back to where the numeric column was mistyped. The usual culprit is an auto-generated type step that fired before the unpivot.

Dataflow refresh times out on large unpivots

Power Query's M engine is single-threaded and in-process. If you're unpivoting a source with hundreds of columns and millions of rows, the engine will struggle. The fix is to either:

• Pre-filter the source rows before the unpivot (reduce row count)
• Pre-select only the period columns you actually need (reduce column count before unpivot)
• Move the operation to a Spark notebook and use Dataflow Gen2 only for the final type-fixing and write

`Table.Group` produces different row counts on each refresh

This is almost always a data issue — the source has duplicate rows that produce inconsistent grouping results. Add a diagnostic step before the Table.Group using Table.Distinct on your key columns to verify uniqueness, or examine the SourceRowCount column from your aggregation to spot periods with unexpectedly high row counts.

Schema mismatch error when refreshing after source adds a new column

If your source table gained a column and you're using Table.UnpivotOtherColumns, that new column will be treated as an attribute to unpivot. If the Delta table already exists with the old schema, a string column appearing in PeriodKey that doesn't look like a period name will corrupt downstream parsing. Fix by adding an explicit Table.SelectColumns step immediately after the source, whitelisting only the known columns.

---

Summary & Next Steps

You now have a complete toolkit for taking wide, denormalized, or awkwardly shaped source data and transforming it into clean, typed Delta tables using Dataflow Gen2's Power Query engine. The key ideas to carry forward:

• Unpivot strategy drives everything downstream. Choose Table.UnpivotOtherColumns for flexible schemas, use List.Select with pattern matching for dynamic column identification, and always add a Table.SelectColumns guard before the unpivot.
• Type fixing is not optional. Every unpivot operation requires an explicit type declaration before the Delta write. Never trust auto-detected types on reshaping operations.
• Table.Group is more powerful than the UI exposes. The sub-table function pattern lets you compute multiple related aggregations from the same intermediate value, which is both more efficient and more readable than stacking separate aggregation steps.
• Final schema lock before output. End every query with Table.SelectColumns (to control column order and exclude any intermediate columns) followed by Table.TransformColumnTypes (to enforce types). This is your contract with the Delta table.
• Know when to hand off to Spark. Dataflow Gen2 is excellent for structural reshaping on moderate data volumes. Window functions, large joins, and MERGE semantics belong in PySpark.

From here, you might want to explore how to parameterize these Dataflow Gen2 queries so the same transformation logic can serve multiple source entities, or how to chain dataflow refreshes into a full pipeline for orchestrated end-to-end loads. And once your silver Delta tables are clean and correctly typed, you're ready to build the analytical Gold layer — whether that's with PySpark star schema construction or Direct Lake semantic model connections for Power BI.