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:
You should be comfortable with:
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 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.
Type 1 makes sense for attributes where the current value is the only correct value, or where history genuinely doesn't matter. Good candidates:
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 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.
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 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
Before implementing anything, decide on your surrogate key approach. In Power Query without a database sequence, you have two practical options:
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
Production Note: For truly robust surrogate key generation in Power Query, consider using
Binary.ToText(Binary.From(text), BinaryEncoding.Hex)to build hex-encoded keys, or better yet, let your database layer assign integer sequences and use Power Query purely for transformation logic. The hash approach above is deterministic and collision-resistant for realistic dimension sizes.
The Type 2 implementation has four logical steps:
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
Warning: The
Table.Combinein Step 7 requires all tables to have compatible schemas. If your dimension has been modified or columns added since it was first created, you may get errors here. Add a schema normalization step before combining to ensure all tables have exactly the same columns in the same order.
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 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.
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 |
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.
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
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:
Detect changes: Write M code to compare the new snapshot to the existing dimension's current rows and identify:
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.
Apply Type 1 to ProductName: Update the ProductName value for P003 across ALL rows (current and historical).
Generate surrogate keys for all new rows using the ProductID + EffectiveDate hash pattern.
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.
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
)
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)
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.
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)
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.
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.
Learning Path: Advanced M Language