Merging Slowly Changing Dimensions in Power Query: Tracking Historical Changes with Type 1 and Type 2 SCD Patterns

Introduction

Picture this: your sales team is celebrating. Revenue is up 22% in Q3 compared to Q2. Your CEO wants to know which region drove that growth. You pull the data — but six months ago, you restructured your territory assignments and moved 40 sales reps between regions. Your customer dimension table just has the current region for each customer. You have no way to answer the question accurately. The historical context is gone.

This is the slowly changing dimension problem, and it's one of the most consequential data modeling challenges in analytics work. Slowly changing dimensions (SCDs) describe how the attributes of dimensional records — customer addresses, product categories, employee job titles, regional assignments — change over time. Handle them wrong and your historical analysis is permanently corrupted. Handle them right and your data warehouse becomes a genuine time machine, letting you slice historical data by the conditions that existed at that moment in time.

In this lesson, you're going to learn how to implement Type 1 and Type 2 SCD merge patterns entirely within Power Query. We'll build a realistic customer dimension scenario with source updates arriving in batches, and you'll walk away with complete, production-ready M code patterns for both update strategies — along with a clear understanding of why you'd choose one over the other, where each one breaks down, and how to optimize them for real datasets.

What you'll learn:

• The architectural difference between Type 1 (overwrite) and Type 2 (version) SCD patterns and when each is appropriate
• How to build a Type 1 merge that identifies changed records, overwrites stale values, and preserves unchanged rows — all in Power Query
• How to implement a full Type 2 versioning pipeline that creates new records for changed attributes while closing out old versions with an effective date range
• How to handle surrogate key generation and effective/expiry date management in M without a relational database engine
• Performance trade-offs, common edge cases (deletions, null handling, multi-column change detection), and when Power Query is the wrong tool for this job

---

Prerequisites

This lesson assumes you are comfortable with:

• Power Query fundamentals: loading tables, applied steps, column operations
• Table.NestedJoin, Table.ExpandTableColumn, and join kinds (left outer, inner, anti-join)
• Writing M functions and using let...in expressions
• Basic data warehousing concepts: what a dimension table is, what a fact table is, what a surrogate key does
• Working knowledge of how Power Query loads data in refresh cycles

If you've never written a custom M function before, go complete the "Custom Functions in M" lesson first. The patterns here depend heavily on that skill.

---

The Scenario: A Customer Dimension Under Pressure

Throughout this lesson, we'll work with a customer dimension table from a fictional B2B SaaS company, Meridian Analytics. The dimension tracks customers and their attributes for use in sales and support analytics.

The existing dimension (what's already in your data warehouse or Power Query model) looks like this:

CustomerID	CustomerName	Segment	Region	AccountOwner	ContractTier
C001	Hargrove Industrial	Enterprise	Northeast	Sarah Chen	Gold
C002	Pelican Media Group	SMB	Southeast	Marcus Reyes	Silver
C003	Vortex Logistics	Mid-Market	Midwest	Sarah Chen	Bronze
C004	Bright Horizon Edu	SMB	West	Jordan Kim	Silver
C005	Cascadia Health	Enterprise	West	Marcus Reyes	Gold

The incoming source update — a fresh extract from your CRM — arrives as:

CustomerID	CustomerName	Segment	Region	AccountOwner	ContractTier
C001	Hargrove Industrial	Enterprise	Northeast	David Park	Platinum
C002	Pelican Media Group	SMB	Southeast	Marcus Reyes	Silver
C003	Vortex Logistics	Enterprise	Midwest	Sarah Chen	Gold
C005	Cascadia Health	Enterprise	West	Jordan Kim	Gold
C006	Nomad Retail Co	SMB	Northwest	Jordan Kim	Bronze

Notice what's happening here:

• C001: AccountOwner changed from Sarah Chen to David Park, ContractTier upgraded to Platinum
• C002: No changes (identical record)
• C003: Segment changed from Mid-Market to Enterprise, ContractTier upgraded to Gold
• C004: Missing from the source — possibly churned or data quality issue
• C005: AccountOwner changed from Marcus Reyes to Jordan Kim
• C006: Brand new customer, not in existing dimension

How we handle C001, C003, and C005 is the entire SCD question. C006 is a straightforward insert. C004 introduces the deletion handling edge case we'll address later.

---

Understanding Type 1 vs. Type 2: The Real Architectural Choice

Before writing a single line of M code, you need to understand what you're actually choosing between — because this is a business decision disguised as a technical one.

Type 1 — Overwrite: When a record changes, you simply update it in place. The dimension table always reflects the current state of the world. No history is preserved. If Sarah Chen handed off Hargrove Industrial to David Park, your dimension table will show David Park as the account owner, and any historical sales records tied to Hargrove Industrial will now appear to have always been owned by David Park.

This sounds wrong — and it often is — but there are legitimate use cases. If you're correcting a data entry error (the customer's name was misspelled), Type 1 is exactly right. You want all history to reflect the correction. If a column is purely operational (like a phone number or email address) and is never used for historical analysis, Type 1 is appropriate. Type 1 is simpler to implement and maintain.

Type 2 — Version: When a record changes, you create a new row in the dimension with updated attributes and a new effective date. The old row is closed out with an expiry date. The dimension table will eventually contain multiple rows per CustomerID — one for each version of that customer's attributes. Fact tables join to the dimension via a surrogate key, and historical facts will correctly join to the version that was active at the time of the transaction.

Type 2 is the right choice when the changed attribute is analytically meaningful over time. Region, Segment, AccountOwner, ContractTier — all of these could be grouping dimensions in a historical report. If someone asks "what was our revenue by contract tier last year vs. this year?", you need Type 2 on ContractTier to answer that correctly.

Design principle: Apply Type 1 to attributes you'd be comfortable retroactively correcting everywhere. Apply Type 2 to attributes that you'd want to filter or group historical data by.

Many real-world dimension tables are hybrid: some columns get Type 1 treatment (correct in place), and others get Type 2 treatment (version on change). We'll build to that complexity.

---

Setting Up the Power Query Environment

We'll work with two data sources loaded as Power Query queries:

Query: ExistingDimension — This represents your current dimension table. In a real implementation, this would be loaded from your data warehouse, a SQL Server table, a SharePoint list, or wherever your authoritative dimension lives.

Query: SourceUpdate — This represents the incoming batch of updates from your source system. In practice, this might come from a CRM API export, a CSV drop, a staging database table, or a delta from a CDC pipeline.

Start by creating both queries with their respective data. In Power Query, you can use Table.FromRows to create them programmatically during development, then replace the source step with your real connection later.

For the ExistingDimension, in the Advanced Editor:

let
    Source = Table.FromRows(
        {
            {"C001", "Hargrove Industrial", "Enterprise", "Northeast", "Sarah Chen", "Gold"},
            {"C002", "Pelican Media Group", "SMB", "Southeast", "Marcus Reyes", "Silver"},
            {"C003", "Vortex Logistics", "Mid-Market", "Midwest", "Sarah Chen", "Bronze"},
            {"C004", "Bright Horizon Edu", "SMB", "West", "Jordan Kim", "Silver"},
            {"C005", "Cascadia Health", "Enterprise", "West", "Marcus Reyes", "Gold"}
        },
        type table [
            CustomerID = text,
            CustomerName = text,
            Segment = text,
            Region = text,
            AccountOwner = text,
            ContractTier = text
        ]
    )
in
    Source

For SourceUpdate:

let
    Source = Table.FromRows(
        {
            {"C001", "Hargrove Industrial", "Enterprise", "Northeast", "David Park", "Platinum"},
            {"C002", "Pelican Media Group", "SMB", "Southeast", "Marcus Reyes", "Silver"},
            {"C003", "Vortex Logistics", "Enterprise", "Midwest", "Sarah Chen", "Gold"},
            {"C005", "Cascadia Health", "Enterprise", "West", "Jordan Kim", "Gold"},
            {"C006", "Nomad Retail Co", "SMB", "Northwest", "Jordan Kim", "Bronze"}
        },
        type table [
            CustomerID = text,
            CustomerName = text,
            Segment = text,
            Region = text,
            AccountOwner = text,
            ContractTier = text
        ]
    )
in
    Source

With these two queries in place, we have everything we need.

---

Building the Type 1 Merge: Overwrite on Change

The Type 1 logic has three components you need to execute correctly:

1. Identify records in the source that already exist in the dimension — these are update candidates
2. Identify records in the source that do NOT exist in the dimension — these are inserts
3. Identify records in the dimension that do NOT exist in the source — these are potential deletions or records to preserve unchanged

Then assemble the final dimension by combining: unchanged existing records + updated records + new records.

Step 1: Identify Unchanged Existing Records

We need the rows in ExistingDimension whose CustomerIDs do not appear in SourceUpdate, plus the rows that appear in both but have no changes. Let's break these into separate queries for clarity.

First, create a query called Type1_UpdatedRecords that takes all source records that match an existing dimension record (inner join) and uses the source values — since source is always the system of record:

let
    // Inner join: records that exist in both tables
    Joined = Table.NestedJoin(
        SourceUpdate,
        {"CustomerID"},
        ExistingDimension,
        {"CustomerID"},
        "ExistingMatch",
        JoinKind.Inner
    ),

    // We only need the source columns — source wins for Type 1
    // Drop the nested table column, we just needed it to confirm match
    UpdatedOnly = Table.RemoveColumns(Joined, {"ExistingMatch"})
in
    UpdatedOnly

This gives us the source version of every customer that already existed. For C001, C002, C003, C005 — we get the source values. For C002, even though nothing changed, we still take the source row (which happens to be identical anyway).

Step 2: Identify Completely New Records (Inserts)

Create a query called Type1_NewRecords using a left anti-join — records in SourceUpdate that have NO match in ExistingDimension:

let
    NewOnly = Table.NestedJoin(
        SourceUpdate,
        {"CustomerID"},
        ExistingDimension,
        {"CustomerID"},
        "ExistingMatch",
        JoinKind.LeftAnti
    ),
    Cleaned = Table.RemoveColumns(NewOnly, {"ExistingMatch"})
in
    Cleaned

This returns only C006 — the brand new customer.

Step 3: Identify Preserved Records (Existing but Not in Source)

Create Type1_PreservedRecords — records in ExistingDimension with no match in SourceUpdate. These are rows that didn't appear in the incoming batch at all:

let
    PreservedOnly = Table.NestedJoin(
        ExistingDimension,
        {"CustomerID"},
        SourceUpdate,
        {"CustomerID"},
        "SourceMatch",
        JoinKind.LeftAnti
    ),
    Cleaned = Table.RemoveColumns(PreservedOnly, {"SourceMatch"})
in
    Cleaned

This returns C004 — the customer that vanished from the source. Whether you include this or exclude it is a business decision (more on deletion handling later). For now, we'll include it to be safe.

Step 4: Assemble the Final Type 1 Dimension

Create the final query CustomerDimension_Type1:

let
    // Combine all three sets
    Combined = Table.Combine({
        Type1_UpdatedRecords,
        Type1_NewRecords,
        Type1_PreservedRecords
    }),

    // Sort for readability (optional in production)
    Sorted = Table.Sort(Combined, {{"CustomerID", Order.Ascending}})
in
    Sorted

Your final Type 1 dimension output:

CustomerID	CustomerName	Segment	Region	AccountOwner	ContractTier
C001	Hargrove Industrial	Enterprise	Northeast	David Park	Platinum
C002	Pelican Media Group	SMB	Southeast	Marcus Reyes	Silver
C003	Vortex Logistics	Enterprise	Midwest	Sarah Chen	Gold
C004	Bright Horizon Edu	SMB	West	Jordan Kim	Silver
C005	Cascadia Health	Enterprise	West	Jordan Kim	Gold
C006	Nomad Retail Co	SMB	Northwest	Jordan Kim	Bronze

C001, C003, and C005 have been updated with their new values. C006 was inserted. C004 was preserved. Clean, simple, and completely unaware of history.

Consolidating Into a Single Query

In production, you often want the entire logic in one query rather than spread across four. Here's the consolidated version:

let
    // Source references
    Existing = ExistingDimension,
    Incoming = SourceUpdate,

    // Records in source that match existing (updates + unchanged)
    UpdatedRecords = 
        Table.RemoveColumns(
            Table.NestedJoin(
                Incoming, {"CustomerID"},
                Existing, {"CustomerID"},
                "_match", JoinKind.Inner
            ),
            {"_match"}
        ),

    // Records in source with no existing match (inserts)
    NewRecords = 
        Table.RemoveColumns(
            Table.NestedJoin(
                Incoming, {"CustomerID"},
                Existing, {"CustomerID"},
                "_match", JoinKind.LeftAnti
            ),
            {"_match"}
        ),

    // Records in existing with no source match (preserved/missing)
    PreservedRecords = 
        Table.RemoveColumns(
            Table.NestedJoin(
                Existing, {"CustomerID"},
                Incoming, {"CustomerID"},
                "_match", JoinKind.LeftAnti
            ),
            {"_match"}
        ),

    // Final union
    FinalDimension = Table.Sort(
        Table.Combine({UpdatedRecords, NewRecords, PreservedRecords}),
        {{"CustomerID", Order.Ascending}}
    )
in
    FinalDimension

---

Building the Type 2 Merge: Version on Change

Type 2 is significantly more complex. The output isn't just "the current state" — it's a full history of all states. Each version of a customer record gets:

• A surrogate key (a unique integer ID for that specific row, distinct from CustomerID)
• An EffectiveDate (when this version became current)
• An ExpiryDate (when this version was superseded — or a sentinel value like 9999-12-31 if it's current)
• An IsCurrent flag (boolean convenience column)

Your fact table (sales transactions, support tickets, etc.) stores the surrogate key, not the CustomerID. When you analyze historical facts, the join to the dimension gives you the exact version of the customer attributes that were active at the time of the transaction.

The Architecture Shift: State Persistence

Here's the critical challenge with Type 2 in Power Query: the pattern requires reading the existing dimension (with all its historical versions), comparing against the incoming update, and producing a new version of the dimension that includes both old closed-out versions and new open versions.

This means your ExistingDimension for Type 2 is fundamentally different — it already contains multiple rows per CustomerID and has the surrogate key / effective date columns baked in.

Let's redefine our starting point. Here's what the Type 2 existing dimension looks like after a few rounds of updates have already occurred:

let
    Source = Table.FromRows(
        {
            {1, "C001", "Hargrove Industrial", "Enterprise", "Northeast", "Sarah Chen", "Gold",     #date(2022,1,1), #date(2023,6,14), false},
            {2, "C001", "Hargrove Industrial", "Enterprise", "Northeast", "Raj Patel",  "Gold",     #date(2023,6,15), #date(2024,3,31), false},
            {3, "C002", "Pelican Media Group", "SMB",        "Southeast", "Marcus Reyes","Silver",  #date(2022,1,1), #date(9999,12,31), true},
            {4, "C003", "Vortex Logistics",    "Mid-Market", "Midwest",   "Sarah Chen", "Bronze",   #date(2022,1,1), #date(9999,12,31), true},
            {5, "C004", "Bright Horizon Edu",  "SMB",        "West",      "Jordan Kim", "Silver",   #date(2022,1,1), #date(9999,12,31), true},
            {6, "C005", "Cascadia Health",     "Enterprise", "West",      "Marcus Reyes","Gold",    #date(2022,1,1), #date(9999,12,31), true}
        },
        type table [
            SurrogateKey = Int64.Type,
            CustomerID = text,
            CustomerName = text,
            Segment = text,
            Region = text,
            AccountOwner = text,
            ContractTier = text,
            EffectiveDate = date,
            ExpiryDate = date,
            IsCurrent = logical
        ]
    )
in
    Source

Notice that C001 already has two historical versions. The current version has IsCurrent = true and ExpiryDate = 9999-12-31.

Step 1: Isolate Current Versions

We only want to compare incoming source records against the current version of each customer. Historical versions are frozen — we never touch them.

let
    CurrentVersions = Table.SelectRows(
        ExistingDimension_Type2,
        each [IsCurrent] = true
    )
in
    CurrentVersions

Step 2: Detect Actually Changed Records

This is the intellectual core of Type 2. We need to compare the incoming source against the current dimension versions and identify rows where at least one tracked column has actually changed. If nothing changed, we don't create a new version.

Create a query Type2_ChangedDetection:

let
    Current = Table.SelectRows(ExistingDimension_Type2, each [IsCurrent] = true),
    Incoming = SourceUpdate,

    // Join source to current dimension on CustomerID
    Joined = Table.NestedJoin(
        Incoming, {"CustomerID"},
        Current, {"CustomerID"},
        "CurrentVersion",
        JoinKind.Inner
    ),

    // Expand only the columns we want to compare
    Expanded = Table.ExpandTableColumn(
        Joined,
        "CurrentVersion",
        {"Segment", "Region", "AccountOwner", "ContractTier", "SurrogateKey"},
        {"Existing.Segment", "Existing.Region", "Existing.AccountOwner", "Existing.ContractTier", "Existing.SurrogateKey"}
    ),

    // Add a change flag: true if ANY tracked attribute differs
    WithChangeFlag = Table.AddColumn(
        Expanded,
        "HasChanged",
        each 
            [Segment] <> [Existing.Segment] or
            [Region] <> [Existing.Region] or
            [AccountOwner] <> [Existing.AccountOwner] or
            [ContractTier] <> [Existing.ContractTier],
        type logical
    ),

    // Keep only records where something actually changed
    ChangedOnly = Table.SelectRows(WithChangeFlag, each [HasChanged] = true)
in
    ChangedOnly

Watch out for null comparisons. In M, null <> "somevalue" evaluates to null (not true), because M uses three-valued logic for null comparisons. If your dimension can contain null values in tracked columns, your change detection will silently fail for null-to-value and value-to-null transitions. Replace comparisons with a null-safe version: ([Segment] ?? "") <> ([Existing.Segment] ?? ""). The null coalescing operator ?? returns the right side if the left is null.

Step 3: Close Out Superseded Current Versions

For every changed record, we need to find the current version in the existing dimension and update its ExpiryDate to yesterday (or whatever your effective date convention is) and set IsCurrent = false.

We'll use today's date as the new effective date, meaning yesterday is the expiry for the old version. In practice you might parameterize this from the batch date of the incoming file.

let
    // Get the surrogate keys of versions being superseded
    ChangedSurrogateKeys = List.Buffer(
        Table.SelectRows(
            Type2_ChangedDetection,
            each [HasChanged] = true
        )[Existing.SurrogateKey]
    ),

    // Today's date for the boundary
    Today = Date.From(DateTime.LocalNow()),
    Yesterday = Date.AddDays(Today, -1),

    // Update the existing dimension: close out superseded current rows
    DimensionWithClosedVersions = Table.TransformRows(
        ExistingDimension_Type2,
        each if List.Contains(ChangedSurrogateKeys, [SurrogateKey]) then
            Record.TransformFields(
                _,
                {
                    {"ExpiryDate", each Yesterday},
                    {"IsCurrent", each false}
                }
            )
        else
            _
    )
in
    Table.FromRecords(DimensionWithClosedVersions)

A few things worth understanding here:

Table.TransformRows iterates every row and returns a modified record. The _ pattern inside the else branch returns the row unchanged. Record.TransformFields surgically modifies specific fields in a record without touching the others.

List.Buffer on the surrogate key list prevents it from being re-evaluated for every row in the List.Contains check. Without buffering, a 10,000-row dimension would evaluate ChangedSurrogateKeys 10,000 times — a severe performance problem.

Step 4: Generate New Version Rows

Now we need to create the new version rows for changed customers, and also handle the insert case (new customers appearing for the first time).

First, let's determine the next available surrogate key. In a real DW, this would come from a sequence in your database. In Power Query, we derive it from the existing dimension:

let
    MaxKey = List.Max(ExistingDimension_Type2[SurrogateKey]),
    NextKeyStart = MaxKey + 1
in
    NextKeyStart

Now build the new version rows:

let
    Today = Date.From(DateTime.LocalNow()),
    MaxKey = List.Max(ExistingDimension_Type2[SurrogateKey]),

    // Get all changed records with their new values
    ChangedRecords = Table.SelectRows(Type2_ChangedDetection, each [HasChanged] = true),

    // Select only the source columns (drop the comparison columns)
    NewVersionBase = Table.SelectColumns(
        ChangedRecords,
        {"CustomerID", "CustomerName", "Segment", "Region", "AccountOwner", "ContractTier"}
    ),

    // Get completely new customers (left anti join on current versions)
    CurrentVersions = Table.SelectRows(ExistingDimension_Type2, each [IsCurrent] = true),
    NewCustomers = Table.RemoveColumns(
        Table.NestedJoin(
            SourceUpdate, {"CustomerID"},
            CurrentVersions, {"CustomerID"},
            "_match", JoinKind.LeftAnti
        ),
        {"_match"}
    ),

    // Combine changed-record new versions + new customer inserts
    AllNewRows = Table.Combine({NewVersionBase, NewCustomers}),

    // Add index for surrogate key generation
    WithIndex = Table.AddIndexColumn(AllNewRows, "_index", MaxKey + 1, 1, Int64.Type),

    // Add dimension metadata columns
    WithSurrogateKey = Table.RenameColumns(WithIndex, {{"_index", "SurrogateKey"}}),
    WithEffectiveDate = Table.AddColumn(WithSurrogateKey, "EffectiveDate", each Today, type date),
    WithExpiryDate = Table.AddColumn(WithEffectiveDate, "ExpiryDate", each #date(9999, 12, 31), type date),
    WithIsCurrent = Table.AddColumn(WithExpiryDate, "IsCurrent", each true, type logical),

    // Reorder columns to match dimension schema
    Reordered = Table.ReorderColumns(
        WithIsCurrent,
        {"SurrogateKey", "CustomerID", "CustomerName", "Segment", "Region", 
         "AccountOwner", "ContractTier", "EffectiveDate", "ExpiryDate", "IsCurrent"}
    )
in
    Reordered

Step 5: Assemble the Complete Type 2 Dimension

Finally, combine the updated historical records (with closed-out versions) with the new version rows:

let
    // Existing dimension with superseded records closed out
    HistoricalBase = Type2_ClosedVersions,  // from Step 3

    // New version rows for changes + inserts
    NewVersionRows = Type2_NewVersionRows,  // from Step 4

    // Union everything together
    CompleteDimension = Table.Sort(
        Table.Combine({HistoricalBase, NewVersionRows}),
        {{"CustomerID", Order.Ascending}, {"EffectiveDate", Order.Ascending}}
    )
in
    CompleteDimension

The output now shows the full history:

SK	CustomerID	Segment	AccountOwner	ContractTier	EffectiveDate	ExpiryDate	IsCurrent
1	C001	Enterprise	Sarah Chen	Gold	2022-01-01	2023-06-14	false
2	C001	Enterprise	Raj Patel	Gold	2023-06-15	2024-03-31	false
7	C001	Enterprise	David Park	Platinum	2025-07-15	9999-12-31	true
3	C002	SMB	Marcus Reyes	Silver	2022-01-01	9999-12-31	true
4	C003	Mid-Market	Sarah Chen	Bronze	2022-01-01	2025-07-14	false
8	C003	Enterprise	Sarah Chen	Gold	2025-07-15	9999-12-31	true
5	C004	SMB	Jordan Kim	Silver	2022-01-01	9999-12-31	true
6	C005	Enterprise	Marcus Reyes	Gold	2022-01-01	2025-07-14	false
9	C005	Enterprise	Jordan Kim	Gold	2025-07-15	9999-12-31	true
10	C006	SMB	Jordan Kim	Bronze	2025-07-15	9999-12-31	true

This dimension now lets you answer historical questions correctly. A sales transaction for C003 from before today joins to surrogate key 4, giving you "Mid-Market / Bronze." A transaction from today onwards joins to surrogate key 8, giving you "Enterprise / Gold."

---

Handling the Hard Edge Cases

The Deletion Problem

C004 (Bright Horizon Edu) disappeared from the source. What do you do? There are three common strategies:

Option A: Soft-delete flag. Add an IsActive column to the dimension. When a record is missing from source, set IsActive = false on the current version. The record remains in the dimension and its history is intact.

Option B: Close out and leave. For Type 2, close out the current version with yesterday's expiry date. No new version is created. The customer's history ends cleanly.

Option C: Preserve and ignore. Do nothing. The dimension record stays open indefinitely. This is often appropriate when source extracts are partial (not a full snapshot), because absence from the extract doesn't necessarily mean deletion.

In Power Query, Option A looks like this addition to your Type 1 merge:

// Identify records in existing that are absent from source
MissingFromSource = Table.NestedJoin(
    ExistingDimension, {"CustomerID"},
    SourceUpdate, {"CustomerID"},
    "_match", JoinKind.LeftAnti
),
MissingCleaned = Table.RemoveColumns(MissingFromSource, {"_match"}),
SoftDeleted = Table.AddColumn(MissingCleaned, "IsActive", each false, type logical),

// Records that match source are still active
ActiveRecords = Table.AddColumn(
    Table.Combine({UpdatedRecords, NewRecords}),
    "IsActive", each true, type logical
)

Multi-Column Change Detection with a Hash

When your dimension has 15 tracked columns, writing [Col1] <> [ExistingCol1] or [Col2] <> [ExistingCol2] or ... for every column is verbose and error-prone. A cleaner pattern is to hash the concatenated tracked values and compare hashes.

let
    TrackedColumns = {"Segment", "Region", "AccountOwner", "ContractTier"},

    // Add a hash column to both tables
    AddHash = (tbl as table) =>
        Table.AddColumn(
            tbl,
            "AttributeHash",
            each Text.Combine(
                List.Transform(
                    TrackedColumns,
                    (col) => Text.From(Record.Field(_, col) ?? "")
                ),
                "|"
            ),
            type text
        ),

    HashedExisting = AddHash(ExistingDimension),
    HashedIncoming = AddHash(SourceUpdate)
in
    // Now join and compare AttributeHash columns instead of individual fields
    HashedExisting

Handling Type 1 and Type 2 Columns in the Same Dimension

Real dimensions often mix strategies. For Meridian Analytics, suppose CustomerName is Type 1 (correct errors in place, always reflect current name) while Segment, Region, and ContractTier are Type 2 (preserve history).

In the Type 2 pipeline, after identifying changed records, you need one additional step: go back through the existing historical records and apply Type 1 updates to the Type 1 columns without creating new versions.

let
    // For Type 1 columns, update ALL versions of changed customers
    // not just the current version
    ChangedCustomerIDs = List.Buffer(
        Table.SelectRows(Type2_ChangedDetection, each [HasChanged] = true)[CustomerID]
    ),

    // Merge latest CustomerName into all historical rows for changed customers
    LatestNames = Table.SelectColumns(
        SourceUpdate,
        {"CustomerID", "CustomerName"}
    ),

    UpdatedHistory = Table.Join(
        ExistingDimension_Type2,
        {"CustomerID"},
        LatestNames,
        {"CustomerID"},
        JoinKind.LeftOuter
    )
    // Then resolve the CustomerName column priority...
in
    UpdatedHistory

This gets complex quickly. In practice, many teams handle this by keeping Type 1 columns in a separate "current state" table joined at query time, rather than physically updating all historical rows. This is a valid architectural shortcut when you're managing the SCD pattern in Power Query rather than a proper ETL tool.

---

Hands-On Exercise

Now it's your turn to build this end-to-end. Here's a scenario that extends the patterns you've learned.

Exercise: The Product Dimension SCD

You're building a product dimension for an e-commerce analytics system. Products can change price tier, category assignment, brand relationship, and fulfillment status over time.

ExistingDimension (Type 2 format):

SurrogateKey	ProductID	ProductName	Category	PriceTier	Brand	IsCurrent	EffectiveDate	ExpiryDate
1	P001	Apex Running Shoe	Footwear	Premium	NorthPeak	true	2023-01-01	9999-12-31
2	P002	Canvas Backpack 28L	Bags	Standard	NorthPeak	true	2023-01-01	9999-12-31
3	P003	Trail Jacket Pro	Outerwear	Premium	Stormgate	true	2023-01-01	9999-12-31
4	P004	Merino Base Layer	Apparel	Standard	NorthPeak	true	2023-01-01	9999-12-31

SourceUpdate (incoming from PIM system):

ProductID	ProductName	Category	PriceTier	Brand
P001	Apex Running Shoe	Footwear	Ultra	NorthPeak
P002	Canvas Backpack 28L	Bags	Standard	NorthPeak
P003	Trail Jacket Pro	Outerwear	Premium	NorthPeak
P004	Merino Base Layer	Apparel	Premium	Stormgate
P005	Hydration Vest Elite	Accessories	Premium	Stormgate

Tasks:

1. Implement a complete Type 2 merge. ProductName is Type 1 (correct in place). Category, PriceTier, and Brand are Type 2 (version on change).

2. P003 changed its Brand from Stormgate to NorthPeak. P001 upgraded PriceTier to Ultra. P004 changed both PriceTier and Brand. P005 is new.

3. Your final dimension should have 9 rows: 4 original rows (3 closed out, 1 unchanged) plus 4 new version rows (for P001, P003, P004 changes + P005 insert).

4. Bonus: Parameterize the effective date so the logic works correctly when run from an older batch file. Create a query parameter BatchDate of type date and use it instead of Date.From(DateTime.LocalNow()).

Verify your output: The current version of P001 should have SurrogateKey 6 (or 5 depending on your ordering), EffectiveDate = BatchDate, PriceTier = "Ultra", and IsCurrent = true. The historical version (SurrogateKey 1) should have ExpiryDate = BatchDate - 1 day and IsCurrent = false.

---

Common Mistakes & Troubleshooting

Mistake 1: Joining on Non-Unique Keys

The Type 2 dimension has multiple rows per CustomerID. If you accidentally join your fact table to the dimension on CustomerID instead of SurrogateKey, you'll get a many-to-many explosion — each transaction will match multiple dimension rows. Always join facts to Type 2 dimensions on the surrogate key.

In Power Query, a quick diagnostic: Table.RowCount(Table.Distinct(YourDimension, {"SurrogateKey"})) should equal Table.RowCount(YourDimension). If it doesn't, you have duplicate surrogate keys — a critical data quality issue.

Mistake 2: Re-evaluating Source Tables Multiple Times

Power Query doesn't cache intermediate results by default. If you reference ExistingDimension five times across your merge queries, it may be loaded and evaluated five times. For large tables against slow sources (SharePoint, REST APIs, large Excel files), this compounds into unacceptable refresh times.

Solution: Use Table.Buffer() at the start of your main merge query to force evaluation once and cache in memory. Be aware that Table.Buffer increases memory usage — don't buffer multi-million-row tables.

Mistake 3: Surrogate Key Gaps and Conflicts

Generating surrogate keys with Table.AddIndexColumn(tbl, "SurrogateKey", MaxExistingKey + 1, 1) works fine in isolation, but can create conflicts if:

• Multiple Power Query refreshes run simultaneously (rare in Power BI, common in Fabric pipelines)
• The source table maximum key changes between when you read it and when you write the new rows back

For true production safety, surrogate key generation should be delegated to a database sequence or identity column. Use Power Query to prepare the data shape, but let the database engine handle key assignment on write.

Mistake 4: Incorrect Date Arithmetic

Date.AddDays(Date.From(DateTime.LocalNow()), -1) gives you yesterday in the local timezone of the machine running the refresh. If your Power BI gateway is in UTC and your business operates in Pacific Time, you might close out a version "yesterday" when the business hasn't ended today yet.

For production pipelines, establish a canonical batch date that comes from the source file metadata or a parameter, not from the refresh machine's clock.

Mistake 5: Not Handling the "No Changes" Refresh

What happens when you run your Type 2 merge and nothing in the source has changed? Your change detection returns zero rows. Your new version rows query has zero rows. Your surrogate key calculation becomes MaxKey + 1 with nothing to attach it to.

Test this scenario explicitly. Table.Combine({HistoricalBase, EmptyNewVersionRows}) should work fine — you're just combining with a zero-row table. But List.Max({}) returns null, so null + 1 will error in your surrogate key calculation. Guard with List.Max(ExistingDimension[SurrogateKey]) ?? 0.

Mistake 6: Power Query Isn't Always the Right Tool

This needs to be said clearly. Power Query's SCD capabilities are real and production-viable for small-to-medium datasets with batch processing needs. But if you're managing:

• Dimensions with millions of rows
• High-frequency update cycles (multiple times per day)
• Complex multi-attribute Type 2 detection across 20+ columns
• Concurrent writes from multiple systems
• Regulatory audit trail requirements

...then you need a proper ETL/ELT platform. Azure Data Factory, dbt (with its snapshot functionality), Informatica, or even Python-based pipelines will give you better reliability, observability, and performance at scale. Power Query excels at being the "last mile" transformer within Power BI. It's not a substitute for a data engineering platform when the data volumes and complexity warrant one.

---

Summary & Next Steps

You've now built both major SCD patterns from scratch in Power Query. Let's consolidate the key concepts:

Type 1 is the overwrite pattern. You identify three record populations — updates/unchanged (from source), inserts (new in source), and preserved (absent from source) — and union them into the final dimension. It's straightforward and appropriate for non-analytically-significant attribute changes. The entire logic collapses into three nested joins and a Table.Combine.

Type 2 is the versioning pattern. It requires an existing dimension that already carries surrogate keys, effective dates, and expiry dates. You isolate current versions, detect genuine changes via column comparison (or hashing), close out superseded rows by updating their expiry date and IsCurrent flag, generate new version rows with incremented surrogate keys and today's effective date, and union everything back together. It preserves the full history of attribute states, enabling accurate historical slicing.

The critical design choices you must make before implementing:

• Which columns get Type 1 treatment vs. Type 2 treatment (business decision, not technical)
• How to handle deleted source records (soft-delete flag, close-out, or preserve)
• Where surrogate keys come from (Power Query index vs. database sequence)
• What "today" means in your pipeline (system clock vs. batch date parameter)
• Whether Power Query is the right tool at your data scale and frequency

Where to go from here:

The natural next step is understanding how to write this merged dimension back out to a persistent store. Power Query's transformation layer is read-oriented by design — you'll need to combine it with Power Automate, a Dataflow Gen2 in Microsoft Fabric, or a custom Python/SQL write-back layer to make the pattern truly persistent across refresh cycles. The lesson "Power Query with Dataflows: Persisting Transformed Data to Storage" covers exactly that pattern.

If you want to push deeper on the M language patterns used here, study Table.TransformRows, Record.TransformFields, and the buffer functions — these are the workhorses of complex merge operations and are largely underdocumented in official references.

Finally, for teams working at scale, the lesson "When to Move Beyond Power Query: Evaluating dbt, ADF, and Spark for Data Transformation" will help you identify the inflection points where Power Query hands off to purpose-built data engineering tools — and how to architect the boundary cleanly.