Implementing Slowly Changing Dimensions in Power Query M: Type 1, Type 2, and Hybrid Strategies for Historical Data Tracking

Introduction

You've built a clean customer dimension table. Addresses are tidy, segments are correct, and your reports look great. Then six months later, your VP of Sales asks a question that should be simple: "Which customers were classified as 'Enterprise' when they made their first purchase, versus which ones were upgraded to Enterprise later?" You open your dimension table and realize you can't answer that question. The current address is there. The current segment is there. But the history? Gone. Overwritten silently, every time the pipeline ran.

This is the slowly changing dimension problem, and it's one of the most consequential design decisions in any analytical data pipeline. Whether you're building a data warehouse in Fabric, maintaining a Power BI semantic model, or wrangling data in Power Query for an Excel-based reporting system, your choice of SCD strategy determines what questions you can and cannot answer — forever. The wrong choice doesn't just make reports slightly wrong; it makes entire classes of questions permanently unanswerable.

By the end of this lesson, you'll be able to implement all three major SCD strategies directly in Power Query M, understand the trade-offs deeply enough to make good design decisions, and build a hybrid approach that handles mixed requirements in a single dimension table.

What you'll learn:

• The mechanics of Type 1 (overwrite), Type 2 (full history), and Type 3 (previous value) patterns and when each is appropriate
• How to implement Type 1 SCD using M's Table.Group and merge patterns
• How to build a full Type 2 SCD implementation with surrogate keys, effective dates, and current-row flags
• How to construct a hybrid dimension that applies different strategies to different attributes in the same table
• How to detect incoming changes, handle late-arriving data, and avoid the most common performance pitfalls

---

Prerequisites

You should be comfortable with:

• Power Query M fundamentals: let/in expressions, custom functions, record and list operations
• Table operations: merging, grouping, appending, adding custom columns
• Basic data warehouse concepts: dimensions, surrogate keys, natural keys
• Working familiarity with what SCDs are conceptually — this lesson is about implementation, not the theory from scratch

---

Understanding the Source Data Pattern

Before writing a single line of M, let's establish the scenario we'll work with throughout this lesson. You're managing a customer dimension for a B2B SaaS company. Your source system exports a daily snapshot of the customer table as a CSV or database query. Each row represents the current state of a customer record.

Here's a representative source snapshot (think of this as what arrives in your Power Query source on any given day):

CustomerID | CustomerName          | Segment    | Region    | ContractValue | AccountOwner
-----------|----------------------|------------|-----------|---------------|-------------
C001       | Meridian Healthcare  | Enterprise | Northeast | 150000        | Sarah Chen
C002       | Bright Futures LLC   | SMB        | Southwest | 12000         | Tom Nguyen
C003       | Apex Industrial      | Mid-Market | Midwest   | 45000         | Sarah Chen
C004       | Cascade Ventures     | SMB        | West      | 8500          | Tom Nguyen

Now, three months later, Apex Industrial has grown — their segment changes to Enterprise and their contract value doubles. Cascade Ventures relocates their headquarters to the Northeast. Sarah Chen leaves the company and her accounts transfer to James Park.

The question is: what do you do with those changes? The answer depends entirely on which attributes need history and which ones don't. That's the core design question.

---

Type 1: Overwrite — Implementing the Simple Case Correctly

Type 1 is the "just update it" strategy. When a value changes, you replace the old value with the new one. No history is kept. This sounds simple, but there's a meaningful difference between doing it right and doing it naively.

When Type 1 is Appropriate

Type 1 makes sense for attributes where the current value is the only correct value, or where history genuinely doesn't matter. Good candidates:

• Correcting data entry errors (the old value was always wrong)
• Slowly-changing contact details where historical values have no analytical value
• Derived or calculated attributes that should always reflect current logic

In our scenario, AccountOwner is a reasonable Type 1 candidate. When Sarah Chen's accounts transfer to James Park, you probably don't need to track that Sarah ever owned them — the transfer was administrative, not analytically meaningful.

The Naive Implementation and Why It Fails

The naive approach is to simply overwrite the entire dimension table with the new snapshot. That works for Type 1, but it destroys your Type 2 rows and creates referential integrity problems if you're maintaining any history elsewhere.

The correct Type 1 implementation selectively updates changed values while leaving the rest of the dimension intact.

Here's the M pattern. Assume you have a DimCustomer_Current table (your existing dimension) and CustomerSource_New (today's incoming snapshot):

let
    // Load existing dimension
    ExistingDim = DimCustomer_Current,

    // Load today's source snapshot — only columns managed as Type 1
    NewSnapshot = CustomerSource_New,

    // Select only the natural key and Type 1 attributes from the new snapshot
    Type1Attributes = Table.SelectColumns(NewSnapshot, {
        "CustomerID", "AccountOwner", "Region"
    }),

    // Merge the new Type 1 values onto the existing dimension
    // Left join: keep all existing rows, bring in new values
    Merged = Table.NestedJoin(
        ExistingDim,
        {"CustomerID"},
        Type1Attributes,
        {"CustomerID"},
        "NewValues",
        JoinKind.LeftOuter
    ),

    // Expand the joined columns with a suffix to avoid collision
    Expanded = Table.ExpandTableColumn(
        Merged,
        "NewValues",
        {"AccountOwner", "Region"},
        {"AccountOwner_New", "Region_New"}
    ),

    // Apply Type 1 logic: use new value if available, otherwise keep existing
    UpdatedOwner = Table.AddColumn(Expanded, "AccountOwner_Final", each
        if [AccountOwner_New] <> null
        then [AccountOwner_New]
        else [AccountOwner]
    ),

    UpdatedRegion = Table.AddColumn(UpdatedOwner, "Region_Final", each
        if [Region_New] <> null
        then [Region_New]
        else [Region]
    ),

    // Drop intermediate columns and rename finals
    Cleaned = Table.RemoveColumns(UpdatedRegion, {
        "AccountOwner", "Region",
        "AccountOwner_New", "Region_New"
    }),

    Renamed = Table.RenameColumns(Cleaned, {
        {"AccountOwner_Final", "AccountOwner"},
        {"Region_Final", "Region"}
    })

in
    Renamed

Why this matters: Notice we're using a left outer join from the existing dimension, not the other way around. New source rows that don't already exist in the dimension don't automatically get inserted here — that's handled separately in your dimension load process. This step is purely about updating existing rows.

Building a Change Detection Layer

A production-ready Type 1 implementation should also flag what actually changed, both for auditing and to avoid unnecessary writes. Here's a change detection pattern:

let
    ExistingDim = DimCustomer_Current,
    NewSnapshot = CustomerSource_New,

    // Join existing to new on natural key
    JoinedForComparison = Table.NestedJoin(
        ExistingDim,
        {"CustomerID"},
        NewSnapshot,
        {"CustomerID"},
        "NewRow",
        JoinKind.Inner
    ),

    Expanded = Table.ExpandTableColumn(
        JoinedForComparison,
        "NewRow",
        {"AccountOwner", "Region"},
        {"New_AccountOwner", "New_Region"}
    ),

    // Add a flag that's true if anything Type 1 changed
    WithChangeFlag = Table.AddColumn(Expanded, "HasType1Change", each
        [AccountOwner] <> [New_AccountOwner] or
        [Region] <> [New_Region]
    ),

    // Filter to only the rows that actually changed
    ChangedRows = Table.SelectRows(WithChangeFlag, each [HasType1Change] = true)

in
    ChangedRows

This gives you a delta — only the rows that need updating. In large dimensions with hundreds of thousands of rows, applying updates only to changed rows rather than reprocessing everything can be the difference between a 30-second refresh and a 10-minute one.

---

Type 2: Full History — The Real Engineering Challenge

Type 2 is where the real complexity lives. Instead of overwriting a value, you close the existing row (by setting an end date) and insert a new row representing the new state. Each customer can now have multiple rows in the dimension, each representing a valid period in time.

The result looks like this:

SurrogateKey | CustomerID | CustomerName     | Segment    | EffectiveDate | ExpiryDate  | IsCurrent
-------------|------------|------------------|------------|---------------|-------------|----------
1            | C003       | Apex Industrial  | Mid-Market | 2023-01-01    | 2023-09-30  | FALSE
2            | C003       | Apex Industrial  | Enterprise | 2023-10-01    | 9999-12-31  | TRUE

Designing Your Surrogate Key Strategy

Before implementing anything, decide on your surrogate key approach. In Power Query without a database sequence, you have two practical options:

1. Row-index-based keys: Assign integer keys based on row position in the final output. Simple but fragile — keys change if rows are reordered.
2. Hash-based keys: Generate a deterministic hash from the natural key plus the effective date. Stable and reproducible.

We'll use hash-based surrogate keys because they're idempotent — running the same data through the process twice produces the same key values, which is critical for pipeline reliability.

// Surrogate key function using a hash of CustomerID + EffectiveDate
GenerateSurrogateKey = (naturalKey as text, effectiveDate as date) as text =>
    let
        Combined = naturalKey & Date.ToText(effectiveDate, "yyyyMMdd"),
        // M doesn't have native SHA, so we use a deterministic text key
        // In production, you'd often use a numeric hash or database sequence
        SurrogateKey = Text.Upper(Text.Replace(
            Text.Replace(Combined, "-", ""),
            " ", "_"
        ))
    in
        SurrogateKey

The Core Type 2 Pattern

The Type 2 implementation has four logical steps:

1. Identify rows in the source that don't exist in the dimension at all (new customers)
2. Identify rows where a Type 2-tracked attribute has changed (changed customers)
3. Close the current row for changed customers (set ExpiryDate to yesterday, IsCurrent to FALSE)
4. Insert new rows for both new and changed customers

Here's the complete implementation:

let
    // ── Inputs ──────────────────────────────────────────────────────────────
    ExistingDim = DimCustomer_History,        // Your current SCD2 dimension
    NewSnapshot = CustomerSource_New,          // Today's source extract
    LoadDate    = Date.From(DateTime.LocalNow()),

    // Type 2 tracked attributes — changes here trigger new rows
    Type2Cols = {"Segment", "ContractValue"},

    // ── Step 1: Find brand-new customers (not in dimension at all) ──────────
    ExistingKeys = List.Distinct(Table.Column(ExistingDim, "CustomerID")),

    NewCustomers = Table.SelectRows(
        NewSnapshot,
        each not List.Contains(ExistingKeys, [CustomerID])
    ),

    NewCustomerRows = Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(NewCustomers,
                "EffectiveDate", each LoadDate, type date),
            "ExpiryDate", each #date(9999, 12, 31), type date),
        "IsCurrent", each true, type logical
    ),

    // ── Step 2: Find existing current rows ─────────────────────────────────
    CurrentRows = Table.SelectRows(ExistingDim, each [IsCurrent] = true),

    // ── Step 3: Compare current dimension rows to new snapshot ─────────────
    JoinedForDetection = Table.NestedJoin(
        CurrentRows,
        {"CustomerID"},
        NewSnapshot,
        {"CustomerID"},
        "NewData",
        JoinKind.Inner
    ),

    // Expand only the Type 2 columns from the new snapshot
    ExpandedForDetection = Table.ExpandTableColumn(
        JoinedForDetection,
        "NewData",
        List.Transform(Type2Cols, each _),
        List.Transform(Type2Cols, each _ & "_New")
    ),

    // ── Step 4: Flag rows where any Type 2 attribute changed ───────────────
    WithChangeFlag = Table.AddColumn(
        ExpandedForDetection,
        "HasType2Change",
        each List.AnyTrue(
            List.Transform(Type2Cols, (col) =>
                Record.Field(_, col) <> Record.Field(_, col & "_New")
            )
        )
    ),

    ChangedCurrentRows = Table.SelectRows(WithChangeFlag, each [HasType2Change] = true),

    // ── Step 5: Close the old rows ─────────────────────────────────────────
    // Remove the comparison columns and update ExpiryDate and IsCurrent
    ClosedRows = Table.TransformColumns(
        Table.RemoveColumns(
            ChangedCurrentRows,
            List.Transform(Type2Cols, each _ & "_New") & {"HasType2Change"}
        ),
        {
            {"ExpiryDate", each Date.AddDays(LoadDate, -1), type date},
            {"IsCurrent", each false, type logical}
        }
    ),

    // ── Step 6: Build new rows for changed customers ────────────────────────
    // Get the new snapshot data for the changed CustomerIDs
    ChangedCustomerIDs = List.Distinct(
        Table.Column(ChangedCurrentRows, "CustomerID")
    ),

    ChangedNewData = Table.SelectRows(
        NewSnapshot,
        each List.Contains(ChangedCustomerIDs, [CustomerID])
    ),

    ChangedNewRows = Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(ChangedNewData,
                "EffectiveDate", each LoadDate, type date),
            "ExpiryDate", each #date(9999, 12, 31), type date),
        "IsCurrent", each true, type logical
    ),

    // ── Step 7: Assemble the final dimension ───────────────────────────────
    // Historical rows (not current, unchanged)
    HistoricalRows = Table.SelectRows(ExistingDim, each [IsCurrent] = false),

    // Unchanged current rows
    UnchangedCurrentRows = Table.SelectRows(
        Table.SelectRows(WithChangeFlag, each [HasType2Change] = false),
        each true
    ),
    UnchangedCleaned = Table.RemoveColumns(
        UnchangedCurrentRows,
        List.Transform(Type2Cols, each _ & "_New") & {"HasType2Change"}
    ),

    // Combine all four sets of rows
    AllRows = Table.Combine({
        HistoricalRows,
        ClosedRows,
        UnchangedCleaned,
        ChangedNewRows,
        NewCustomerRows
    }),

    // ── Step 8: Add surrogate keys ─────────────────────────────────────────
    WithSurrogateKey = Table.AddColumn(AllRows, "SurrogateKey", each
        Text.Upper(
            Text.Combine({[CustomerID], Date.ToText([EffectiveDate], "yyyyMMdd")}, "_")
        ),
        type text
    ),

    // Reorder columns for clarity
    Final = Table.ReorderColumns(WithSurrogateKey, {
        "SurrogateKey", "CustomerID", "CustomerName",
        "Segment", "ContractValue", "Region", "AccountOwner",
        "EffectiveDate", "ExpiryDate", "IsCurrent"
    })

in
    Final

Handling the Date Arithmetic

A subtle but important decision: when you close a row, should the ExpiryDate be:

• LoadDate - 1 day (the day before the new row's effective date)?
• LoadDate with an exclusive upper bound convention?

Most practitioners use the "previous day" convention for ExpiryDate and set EffectiveDate of the new row to LoadDate. This means for any given date D, the current-as-of-D row is the one where EffectiveDate <= D AND ExpiryDate >= D. The far-future sentinel date 9999-12-31 ensures the current row always satisfies that condition.

The alternative — exclusive upper bound — uses ExpiryDate < LoadDate in your filter, which is more technically correct but creates confusing half-open intervals that many analysts struggle with when writing ad-hoc queries.

Stick with the inclusive convention and the sentinel date. Your future self will thank you.

---

Type 3: Keeping One Version of History

Type 3 stores the previous value of an attribute alongside the current one. It's limited — you only have one step of history — but it's occasionally the right tool when you need to compare "before and after" a known significant change event.

let
    ExistingDim = DimCustomer_Current,
    NewSnapshot = CustomerSource_New,

    // Type 3 attributes: we'll track previous Segment
    JoinedData = Table.NestedJoin(
        ExistingDim,
        {"CustomerID"},
        NewSnapshot,
        {"CustomerID"},
        "NewData",
        JoinKind.LeftOuter
    ),

    Expanded = Table.ExpandTableColumn(
        JoinedData, "NewData",
        {"Segment"},
        {"Segment_New"}
    ),

    // Where Segment changed, move current to "previous" and apply new value
    WithType3 = Table.AddColumn(
        Table.AddColumn(Expanded,
            "PreviousSegment", each
                if [Segment_New] <> null and [Segment_New] <> [Segment]
                then [Segment]
                else [PreviousSegment]  // preserve existing previous value
        ),
        "Segment_Final", each
            if [Segment_New] <> null
            then [Segment_New]
            else [Segment]
    ),

    Cleaned = Table.RemoveColumns(
        Table.RenameColumns(WithType3, {{"Segment_Final", "Segment"}}),
        {"Segment_New"}
    )

in
    Cleaned

Type 3 is often the wrong choice. It's tempting because it seems like a compromise, but it creates a brittle structure. You lose history the moment a value changes twice, and the "previous" column becomes misleading when records have been updated multiple times. Use Type 2 when history matters. Use Type 1 when it doesn't. Reserve Type 3 for very specific scenarios where you genuinely only care about a single transition — like tracking whether a customer was originally acquired through a specific campaign.

---

The Hybrid Strategy: Mixing SCD Types in One Dimension

Real dimensions don't have a single attribute type. Your customer dimension will have attributes that need Type 1 treatment (fix errors, no history needed), Type 2 treatment (full history is essential), and sometimes Type 3 (one step of history is enough). The hybrid strategy applies the right SCD type to each attribute.

Here's the attribute map for our customer dimension:

Attribute	SCD Type	Reasoning
Segment	Type 2	Critical for revenue analysis; history essential
ContractValue	Type 2	Revenue attribution requires historical values
Region	Type 1	Represents customer's actual location; errors should be corrected
AccountOwner	Type 1	Org changes shouldn't pollute history
CustomerName	Type 2	Legal name changes need to be tracked

Implementing the Hybrid Pattern

The hybrid implementation builds on the Type 2 pattern but adds a final step that applies Type 1 updates across all rows (both historical and current) for Type 1 attributes:

let
    // ── Run the Type 2 process first ───────────────────────────────────────
    // (This produces our dimension with properly versioned Type 2 attributes)
    AfterType2Processing = /* ... your Type 2 M code from above ... */,

    // ── Now apply Type 1 updates across all rows ───────────────────────────
    // Type 1 means we update the value everywhere, even in historical rows
    // This is appropriate for corrections (wrong data) not changes (new data)

    NewSnapshot = CustomerSource_New,

    Type1Attributes = Table.SelectColumns(NewSnapshot, {
        "CustomerID", "Region", "AccountOwner"
    }),

    // Join the Type 1 values to ALL rows in the dimension (not just current)
    MergedWithType1 = Table.NestedJoin(
        AfterType2Processing,
        {"CustomerID"},
        Type1Attributes,
        {"CustomerID"},
        "Type1Updates",
        JoinKind.LeftOuter
    ),

    ExpandedType1 = Table.ExpandTableColumn(
        MergedWithType1,
        "Type1Updates",
        {"Region", "AccountOwner"},
        {"Region_New", "AccountOwner_New"}
    ),

    // Apply Type 1: overwrite with new value wherever available
    AppliedType1 = Table.TransformColumns(
        Table.AddColumn(
            Table.AddColumn(ExpandedType1,
                "Region_Final", each
                    if [Region_New] <> null then [Region_New] else [Region]
            ),
            "AccountOwner_Final", each
                if [AccountOwner_New] <> null then [AccountOwner_New] else [AccountOwner]
        ),
        {}  // no bulk transforms needed
    ),

    // Clean up and rename
    HybridDimension = Table.RenameColumns(
        Table.RemoveColumns(AppliedType1, {
            "Region", "AccountOwner", "Region_New", "AccountOwner_New"
        }),
        {
            {"Region_Final", "Region"},
            {"AccountOwner_Final", "AccountOwner"}
        }
    )

in
    HybridDimension

This is the important conceptual point: Type 1 updates propagate backward through history, Type 2 updates create new rows going forward. When you correct a Region value from "Southwest" to "West" because it was always entered wrong, that correction should appear in every historical row for that customer. When Apex Industrial's Segment changes from Mid-Market to Enterprise because they grew, only their new row should reflect Enterprise — historical rows should still show Mid-Market.

---

Building a Reusable SCD Function Library

Once you've implemented SCD logic for one dimension, you'll want to reuse it. Here's a pattern for building a parameterizable Type 2 function you can call for any dimension:

// ApplyType2SCD: A reusable function for Type 2 processing
// Parameters:
//   existingDim     - your current dimension table
//   newSnapshot     - today's source data
//   naturalKeyCol   - name of the natural key column (text)
//   type2Columns    - list of column names to track as Type 2
//   loadDate        - the effective date for this load

(
    existingDim as table,
    newSnapshot as table,
    naturalKeyCol as text,
    type2Columns as list,
    loadDate as date
) as table =>

let
    // Extract existing natural keys for new-customer detection
    ExistingKeys = List.Distinct(Table.Column(existingDim, naturalKeyCol)),

    // ── New customers ──────────────────────────────────────────────────────
    NewCustomers = Table.SelectRows(
        newSnapshot,
        each not List.Contains(ExistingKeys, Record.Field(_, naturalKeyCol))
    ),
    NewCustomerRowsPrep = Table.AddColumn(NewCustomers, "EffectiveDate", each loadDate, type date),
    NewCustomerRowsPrep2 = Table.AddColumn(NewCustomerRowsPrep, "ExpiryDate", each #date(9999,12,31), type date),
    NewCustomerRows = Table.AddColumn(NewCustomerRowsPrep2, "IsCurrent", each true, type logical),

    // ── Current rows in existing dimension ────────────────────────────────
    CurrentDimRows = Table.SelectRows(existingDim, each [IsCurrent] = true),

    // ── Change detection ──────────────────────────────────────────────────
    Joined = Table.NestedJoin(
        CurrentDimRows, {naturalKeyCol},
        newSnapshot, {naturalKeyCol},
        "_NewData", JoinKind.Inner
    ),

    ExpandedNew = Table.ExpandTableColumn(
        Joined, "_NewData",
        type2Columns,
        List.Transform(type2Columns, each "_chk_" & _)
    ),

    WithChangeFlag = Table.AddColumn(ExpandedNew, "_HasChanged",
        each List.AnyTrue(
            List.Transform(type2Columns, (col) =>
                Record.Field(_, col) <> Record.Field(_, "_chk_" & col)
            )
        )
    ),

    CheckCols = List.Transform(type2Columns, each "_chk_" & _),
    WithChangeFlagClean = Table.RemoveColumns(WithChangeFlag, CheckCols),

    // ── Close changed rows ────────────────────────────────────────────────
    ChangedRows = Table.SelectRows(WithChangeFlagClean, each [_HasChanged] = true),
    ClosedRows = Table.RemoveColumns(
        Table.TransformColumns(ChangedRows, {
            {"ExpiryDate", each Date.AddDays(loadDate, -1), type date},
            {"IsCurrent", each false, type logical}
        }),
        {"_HasChanged"}
    ),

    // ── New rows for changed customers ────────────────────────────────────
    ChangedIDs = List.Distinct(Table.Column(ChangedRows, naturalKeyCol)),
    NewVersionRows = Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(
                Table.SelectRows(newSnapshot, each List.Contains(ChangedIDs, Record.Field(_, naturalKeyCol))),
                "EffectiveDate", each loadDate, type date),
            "ExpiryDate", each #date(9999,12,31), type date),
        "IsCurrent", each true, type logical
    ),

    // ── Unchanged current rows ────────────────────────────────────────────
    UnchangedRows = Table.RemoveColumns(
        Table.SelectRows(WithChangeFlagClean, each [_HasChanged] = false),
        {"_HasChanged"}
    ),

    // ── Historical rows (already closed) ─────────────────────────────────
    HistoricalRows = Table.SelectRows(existingDim, each [IsCurrent] = false),

    // ── Combine all sets ──────────────────────────────────────────────────
    Combined = Table.Combine({
        HistoricalRows,
        ClosedRows,
        UnchangedRows,
        NewVersionRows,
        NewCustomerRows
    })

in
    Combined

Invoke it like this:

let
    Result = ApplyType2SCD(
        DimCustomer_History,
        CustomerSource_Today,
        "CustomerID",
        {"Segment", "ContractValue", "CustomerName"},
        Date.From(DateTime.LocalNow())
    )
in
    Result

---

Hands-On Exercise: Building a Product Dimension with Hybrid SCD

Scenario: Your company sells software licenses. The product catalog changes frequently. Some changes need history (pricing, product tier) and some don't (typo corrections in product names, category reassignments for reporting purposes).

Source data — ProductSource_New:

ProductID | ProductName            | Tier       | Price  | Category     | IsActive
P001      | Analytics Pro          | Enterprise | 2400   | Analytics    | TRUE
P002      | Analytics Starter      | SMB        | 600    | Analytics    | TRUE
P003      | Workflow Automation    | Enterprise | 1800   | Automation   | TRUE
P004      | Reporting Essentials   | SMB        | 300    | Reporting    | TRUE

Existing dimension — DimProduct_History:

SurrogateKey | ProductID | ProductName         | Tier     | Price | Category  | EffectiveDate | ExpiryDate  | IsCurrent
PK001_20230101 | P001    | Analytics Pro       | Mid-Market| 1800  | Analytics | 2023-01-01   | 9999-12-31  | TRUE
PK002_20230101 | P002    | Analytics Starter   | SMB       | 600   | Analytics | 2023-01-01   | 9999-12-31  | TRUE
PK003_20230101 | P003    | Workflow Auto       | Enterprise| 1800  | Automation| 2023-01-01   | 9999-12-31  | TRUE

Your tasks:

1. Detect changes: Write M code to compare the new snapshot to the existing dimension's current rows and identify:

   • P001: Tier changed (Mid-Market → Enterprise) and Price changed (1800 → 2400) — Type 2 change
   • P003: Name corrected (Workflow Auto → Workflow Automation) — Type 1 correction
   • P004: New product — insert as new

2. Apply Type 2 to Tier and Price: Close the existing P001 row (ExpiryDate = yesterday, IsCurrent = FALSE) and create a new P001 row with the new Tier and Price.

3. Apply Type 1 to ProductName: Update the ProductName value for P003 across ALL rows (current and historical).

4. Generate surrogate keys for all new rows using the ProductID + EffectiveDate hash pattern.

5. Verify your output has 5 rows: 1 closed P001, 1 new P001, 1 P002 (unchanged), 1 P003 (name corrected), 1 P004 (new).

Expected final dimension:

SurrogateKey     | ProductID | Tier        | Price | IsCurrent | EffectiveDate
PK001_20230101   | P001      | Mid-Market  | 1800  | FALSE     | 2023-01-01
P001_20231015    | P001      | Enterprise  | 2400  | TRUE      | 2023-10-15
PK002_20230101   | P002      | SMB         | 600   | TRUE      | 2023-01-01
PK003_20230101   | P003      | Enterprise  | 1800  | TRUE      | 2023-01-01
P004_20231015    | P004      | SMB         | 300   | TRUE      | 2023-10-15

Work through this using the patterns from this lesson before checking your approach against the code above. The exercise is designed so that you'll hit the "schema mismatch in Table.Combine" problem — figuring out how to resolve that will solidify your understanding of the whole approach.

---

Common Mistakes & Troubleshooting

Mistake 1: Running the SCD Process Without Idempotency Checks

If your pipeline runs twice on the same day — due to a refresh error, a manual re-run, or a scheduling bug — a naive Type 2 implementation will create duplicate rows. Every "changed" row will be closed and reopened, doubling your historical records.

Fix: Add a check at the start of your process that filters out source rows where the current dimension row already has EffectiveDate = LoadDate. If the effective date matches, the row was already processed today.

// Exclude rows already processed today
CurrentRowsNotYetProcessed = Table.SelectRows(
    CurrentDimRows,
    each [EffectiveDate] <> loadDate
)

Mistake 2: Comparing Nullable or Typed Mismatches

The change detection comparison [Segment] <> [Segment_New] will return null (not false) when either value is null, causing the row to be skipped rather than flagged as changed. Similarly, comparing a number column to a text column from a CSV source will always return true, generating phantom changes.

Fix: Normalize types before comparison, and handle nulls explicitly:

HasChanged = each
    let
        oldVal = Text.From([Segment]),
        newVal = Text.From([Segment_New])
    in
        (oldVal = null) <> (newVal = null) or
        (oldVal <> null and newVal <> null and oldVal <> newVal)

Mistake 3: Forgetting to Handle Late-Arriving Data

What happens when a source system sends you a correction for a record that should have changed three weeks ago, but you're processing it today? If you set EffectiveDate = LoadDate, you'll get a row with the wrong effective date. The historical record from three weeks ago will be wrong.

Fix: Include an EffectiveDate column in your source if the source system tracks when changes actually occurred (separate from when you received them). Use that as your effective date for Type 2 rows, not the load date. This requires more complex logic to handle re-closing rows that span the retroactive effective date, but it produces correct history.

Mistake 4: Table.Combine Schema Mismatches

When combining tables with Table.Combine, Power Query requires all tables to have the same columns. If your new rows don't have a SurrogateKey column (because you add it after combining), or your existing dimension has an extra audit column, you'll get a schema error.

Fix: Either add placeholder columns to all tables before combining, or add all metadata columns (SurrogateKey, audit fields) after the combine step.

// Add placeholder for columns that only some tables have
TableWithPlaceholder = Table.AddColumn(SomeTable, "SurrogateKey",
    each null, type text)

Mistake 5: Performance Collapse on Large Dimensions

Table.NestedJoin on large tables in Power Query can be extremely slow — sometimes O(n²) — because M evaluates lazily and doesn't always use optimized join strategies. On a dimension with 500k rows, this can make your refresh unusable.

Fix: If your dimension is large, push the join logic into your source database using native query folding, or use Power Query's Table.Buffer to materialize tables before joining them:

BufferedDim = Table.Buffer(ExistingDim),
BufferedSource = Table.Buffer(NewSnapshot),
Joined = Table.NestedJoin(BufferedDim, {"CustomerID"}, BufferedSource, ...)

Table.Buffer forces evaluation at that step, preventing Power Query from repeatedly re-evaluating the source during the join.

---

Summary & Next Steps

You now have a complete toolkit for implementing SCD strategies in Power Query M. Let's recap the key design principles:

Type 1 is appropriate for corrections and attributes where history genuinely has no analytical value. Apply it across all rows — current and historical — for each affected customer.

Type 2 is the gold standard for analytically meaningful changes. It creates new rows with effective date ranges, preserves complete history, and enables point-in-time analysis. The implementation is more complex, but the investment pays off every time someone asks a historical question.

Type 3 is a narrow tool for specific use cases. Default to Type 2 unless you have a compelling reason for the limitations that Type 3 imposes.

Hybrid strategies are the reality of production dimensions. Map each attribute to its appropriate SCD type, then apply them in sequence: Type 2 first (to create properly versioned rows), then Type 1 (to propagate corrections across all versions).

Performance matters. Use Table.Buffer on large tables before joining. Consider pushing SCD logic to the database layer if your dimensions have hundreds of thousands of rows. Power Query is an excellent place to express SCD logic when data volumes are moderate; it becomes a bottleneck at scale.

Where to Go Next

• Explore query folding: If your source is SQL Server, Snowflake, or another foldable connector, much of the SCD logic can be pushed to the database engine. Learn how to verify whether your M expressions fold using the Query Diagnostics feature.
• Implement SCD in Fabric Dataflows Gen2: The patterns from this lesson apply directly to Dataflows, which gives you scheduled refresh, managed data destinations, and better performance than desktop Power Query for large datasets.
• Add data vault patterns: If your SCD requirements grow complex — multiple source systems contributing to the same dimension, complex key relationships — explore how Data Vault 2.0 Hubs, Links, and Satellites complement or replace traditional SCD approaches.
• Build a Type 2 fact table: SCDs in dimensions are only half the story. Ensure your fact table uses the correct surrogate keys to join to the right dimension version for each fact row — otherwise, your carefully maintained history will never actually appear in your reports.