Implementing Late-Arriving Facts and Out-of-Order Record Reconciliation in Power Query

Introduction

Picture this: your sales dashboard shows a clean month-end close, your regional managers have signed off on the numbers, and the finance team is preparing for the board presentation. Then, forty-eight hours later, a batch of EDI transactions from a third-party logistics partner lands in your source system — all of them dated last month. Or consider a healthcare claims scenario where insurance adjustments routinely arrive 90 to 120 days after the original service date, silently mutating the revenue picture for periods you've already reported. Welcome to the world of late-arriving facts, one of the genuinely hard problems in data engineering that most Power Query tutorials don't go anywhere near.

Late-arriving facts — records that belong to a prior reporting period but arrive after that period's data has already been processed — are not edge cases. They're a structural feature of almost every real-world operational system. EDI pipelines have transmission delays. ERP systems accumulate transactions in batches. Field teams sync their mobile apps intermittently. And when your Power Query solution quietly overwrites yesterday's mart with today's pull, those backdated records either disappear entirely or corrupt your historical comparisons without any visible error. The damage is invisible until someone asks why March's numbers changed in June.

By the end of this lesson, you'll have a complete mental model for detecting, reconciling, and absorbing late-arriving records in Power Query — without losing historical integrity, without blowing up your refresh performance, and without building a fragile Rube Goldberg machine of nested conditionals that nobody can maintain in six months.

What you'll learn:

• How to design a Power Query pipeline that detects whether incoming records are new, updated, or late-arriving relative to a previously loaded state
• How to implement a watermark-based reconciliation pattern that survives incremental refresh cycles
• How to build a change-data-capture style merge that handles backdated records without corrupting prior-period aggregates
• How to manage slowly changing dimensions in conjunction with late-arriving facts so your historical slice-and-dice stays correct
• How to architect the solution so it scales as record volumes grow, including fold-down strategies to push work toward the source

---

Prerequisites

You should be comfortable with:

• Power Query M syntax at an intermediate-to-advanced level — anonymous functions, List.Accumulate, Table.Group, and recursive queries
• The concept of a slowly changing dimension (SCD) Type 1 vs. Type 2
• Basic data warehousing concepts: fact tables, dimension tables, grain, surrogate keys
• Power BI dataflows or Power BI Desktop with a local data model (the patterns here apply to both)
• Access to a data source you can query incrementally — a SQL database, SharePoint list, or flat-file archive organized by arrival date

---

Understanding the Core Problem Before Writing a Single Line of M

Before jumping into code, you need to deeply understand why this problem is architecturally awkward in Power Query specifically. Most ETL tools — SSIS, dbt, Azure Data Factory — operate in a pipeline model where you can explicitly compare a source snapshot against a target state and emit a delta. Power Query, by design, is a functional transformation engine. It transforms a set of inputs into an output. It doesn't natively maintain state between refresh cycles.

That statelessness is what bites you. When you refresh a Power Query dataset, the query runs from scratch against whatever the source contains right now. If your source is a transactional database and you're pulling with a simple WHERE TransactionDate >= '2024-01-01', you'll catch late-arriving records — but only if they have a TransactionDate in your window. If a transaction dated December 15th arrives in your pipeline on February 3rd, it has a TransactionDate of December 15th and a SystemInsertDate (the timestamp when it actually landed in the source) of February 3rd. A query filtered on TransactionDate won't pick it up for December at all unless you re-pull December. And if you re-pull December, you corrupt your audit trail of what you knew when.

This distinction — business date vs. arrival date — is the conceptual heart of the entire lesson. Internalize it now and everything else will make sense.

Concept	Field Name (typical)	What It Represents
Business Date	TransactionDate, ServiceDate	When the event happened in the real world
Arrival Date	CreatedAt, InsertedTimestamp, ETLLoadDate	When the record entered the source system
Processing Date	RefreshTimestamp	When Power Query saw the record

Your reconciliation strategy has to respect all three.

---

Designing Your Source Schema for Reconcilability

Here's uncomfortable news: if your source system doesn't expose an arrival timestamp on every record, late-arrival detection becomes dramatically harder. The very first thing you should do is audit your source. In SQL Server, that might look like:

SELECT 
    TransactionID,
    TransactionDate,
    CustomerID,
    Amount,
    CreatedAt,         -- when this row was inserted
    ModifiedAt         -- when this row was last updated
FROM dbo.SalesTransactions

If CreatedAt and ModifiedAt don't exist, talk to your source system owner about adding them. If you genuinely can't modify the source, you'll need to build arrival detection yourself using a snapshot comparison pattern — we'll cover that as a fallback later.

Assuming your source has an arrival timestamp, the high-level strategy is:

1. On each refresh, pull all records where ModifiedAt > [last watermark]
2. Detect which of those records have a TransactionDate in a prior reporting period
3. Classify them as late-arriving
4. Merge them into your existing mart — updating aggregates or appending new rows as appropriate
5. Store the new watermark for the next refresh

Let's build each piece.

---

Building a Watermark Table

A watermark is simply a stored value — the timestamp of the most recent record you've successfully processed. On each refresh, you query everything after the watermark, then update the watermark to the current run's maximum arrival timestamp.

In Power Query, you need to store this somewhere durable. Your options, roughly in order of robustness:

1. A dedicated watermark table in your database (best for SQL sources)
2. A SharePoint list or Excel table (fine for medium-complexity solutions)
3. A parameter in the Power Query editor (fragile — parameters reset if the file is shared or moved)
4. A text file in a known location (workable for file-based pipelines)

For a SQL-backed solution, create a table like this:

CREATE TABLE dbo.ETLWatermarks (
    PipelineName    NVARCHAR(100) NOT NULL,
    LastRunAt       DATETIME2    NOT NULL,
    LastRunBy       NVARCHAR(100) NOT NULL,
    RecordsLoaded   INT          NULL,
    CONSTRAINT PK_ETLWatermarks PRIMARY KEY (PipelineName)
);

INSERT INTO dbo.ETLWatermarks VALUES 
    ('SalesTransactions', '2024-01-01 00:00:00', SYSTEM_USER, 0);

Now in Power Query, you read the watermark first:

// Step 1: Read the watermark
let
    Source = Sql.Database("your-server", "your-db"),
    WatermarkTable = Source{[Schema="dbo", Item="ETLWatermarks"]}[Data],
    FilteredWatermark = Table.SelectRows(
        WatermarkTable, 
        each [PipelineName] = "SalesTransactions"
    ),
    CurrentWatermark = FilteredWatermark{0}[LastRunAt]
in
    CurrentWatermark

Name this query LastWatermark. It returns a single datetime value that you'll reference in your main query.

Warning: In Power Query, query dependencies can create circular refresh problems if you're not careful. Keep your watermark query as a pure lookup — it should never depend on the result of your main transformation query.

---

Pulling Incremental Records with Dual-Date Awareness

With the watermark in hand, you can now pull only records that have changed since the last run:

// Step 2: Pull changed records since last watermark
let
    Source = Sql.Database("your-server", "your-db"),
    SalesTable = Source{[Schema="dbo", Item="SalesTransactions"]}[Data],
    
    // Pull everything modified since our last watermark
    // This catches both new records AND updates to existing records
    IncrementalLoad = Table.SelectRows(
        SalesTable,
        each [ModifiedAt] > LastWatermark
    ),
    
    // Add a classification column immediately
    // A record is "late-arriving" if its business date is in a prior period
    // relative to when it was inserted into the source system
    // We define "prior period" as: TransactionDate is more than 2 days 
    // before CreatedAt (adjust threshold for your business context)
    
    CurrentRefreshTime = DateTimeZone.UtcNow(),
    
    WithArrivalClassification = Table.AddColumn(
        IncrementalLoad,
        "ArrivalClassification",
        each 
            let
                businessDate = Date.From([TransactionDate]),
                arrivalDate  = Date.From([CreatedAt]),
                lagDays      = Duration.Days(arrivalDate - businessDate)
            in
                if lagDays > 2 then "LateArriving"
                else if [ModifiedAt] > [CreatedAt] then "Updated"
                else "OnTime"
        ,
        type text
    ),
    
    // Add how many days late this record is
    WithLagDays = Table.AddColumn(
        WithArrivalClassification,
        "ArrivalLagDays",
        each Duration.Days(Date.From([CreatedAt]) - Date.From([TransactionDate])),
        Int32.Type
    )
    
in
    WithLagDays

This gives you a working dataset with every changed record since the watermark, plus metadata about why it changed and how late it is. This metadata is crucial — you'll use it both for operational monitoring and for downstream reconciliation logic.

---

Building the Reconciliation Engine

Now for the heart of the solution. The reconciliation engine needs to answer: for each late-arriving record, what was the state of the mart for the period it belongs to, and how does this new record change that state?

This is where Power Query gets genuinely complex. Let's work through a realistic scenario.

Scenario: You're building a daily sales summary mart at the grain of (SalesRegion, ProductCategory, Date). Your source has late-arriving transactions from field reps who sync their mobile tablets intermittently. A transaction from March 12th might not appear in your pipeline until March 19th.

Your existing mart looks like this:

SalesRegion | ProductCategory | Date       | TotalRevenue | TransactionCount | LastRefreshed
Northeast   | Electronics     | 2024-03-12 | 48,250.00    | 17               | 2024-03-13
Northeast   | Electronics     | 2024-03-13 | 51,100.00    | 19               | 2024-03-14

On March 19th, three late-arriving transactions appear for March 12th — Northeast Electronics, totaling $3,400. Your mart needs to become:

SalesRegion | ProductCategory | Date       | TotalRevenue | TransactionCount | LastRefreshed
Northeast   | Electronics     | 2024-03-12 | 51,650.00    | 20               | 2024-03-19  ← updated
Northeast   | Electronics     | 2024-03-13 | 51,100.00    | 19               | 2024-03-14

Here's how you build the merge. First, load your existing mart:

// Step 3: Load existing mart state
let
    Source = Sql.Database("your-server", "your-db"),
    ExistingMart = Source{[Schema="dbo", Item="SalesSummaryMart"]}[Data]
in
    ExistingMart

Name this query ExistingMart.

Next, aggregate the late-arriving records to the mart grain:

// Step 4: Aggregate late arrivals to mart grain
let
    // Reference our incremental load from Step 2
    LateArrivals = Table.SelectRows(
        IncrementalLoad, 
        each [ArrivalClassification] = "LateArriving"
    ),
    
    // Group to mart grain: Region + Category + BusinessDate
    AggregatedLateArrivals = Table.Group(
        LateArrivals,
        {"SalesRegion", "ProductCategory", "TransactionDate"},
        {
            {"LateRevenue",       each List.Sum([Amount]),   type number},
            {"LateCount",         each Table.RowCount(_),   Int32.Type},
            {"MaxArrivalDate",    each List.Max([CreatedAt]), type datetime}
        }
    ),
    
    // Rename TransactionDate to match mart grain name
    Renamed = Table.RenameColumns(
        AggregatedLateArrivals,
        {{"TransactionDate", "Date"}}
    )
    
in
    Renamed

Now the merge — this is where the reconciliation actually happens:

// Step 5: Reconcile late arrivals into existing mart
let
    // Join late arrivals to existing mart on the grain key
    Joined = Table.NestedJoin(
        ExistingMart,
        {"SalesRegion", "ProductCategory", "Date"},
        AggregatedLateArrivals,
        {"SalesRegion", "ProductCategory", "Date"},
        "LateData",
        JoinKind.LeftOuter
    ),
    
    // Expand the joined late data
    Expanded = Table.ExpandTableColumn(
        Joined,
        "LateData",
        {"LateRevenue", "LateCount", "MaxArrivalDate"},
        {"LateRevenue", "LateCount", "MaxArrivalDate"}
    ),
    
    // Apply the reconciliation: if late data exists for this grain,
    // add it to the existing totals
    Reconciled = Table.TransformColumns(
        Table.AddColumn(
            Expanded,
            "ReconciledRevenue",
            each 
                if [LateRevenue] = null then [TotalRevenue]
                else [TotalRevenue] + [LateRevenue],
            type number
        ),
        {}  // no additional transforms at this step
    ),
    
    ReconciledWithCount = Table.AddColumn(
        Reconciled,
        "ReconciledCount",
        each 
            if [LateCount] = null then [TransactionCount]
            else [TransactionCount] + [LateCount],
        Int32.Type
    ),
    
    ReconciledWithTimestamp = Table.AddColumn(
        ReconciledWithCount,
        "ReconciledRefreshTime",
        each 
            if [MaxArrivalDate] = null then [LastRefreshed]
            else DateTime.From(DateTimeZone.UtcNow()),
        type datetime
    ),
    
    // Select final columns in mart structure
    FinalMart = Table.SelectColumns(
        ReconciledWithTimestamp,
        {
            "SalesRegion", 
            "ProductCategory", 
            "Date", 
            "ReconciledRevenue",
            "ReconciledCount",
            "ReconciledRefreshTime"
        }
    ),
    
    // Rename back to mart column names
    RenamedFinal = Table.RenameColumns(
        FinalMart,
        {
            {"ReconciledRevenue",     "TotalRevenue"},
            {"ReconciledCount",       "TransactionCount"},
            {"ReconciledRefreshTime", "LastRefreshed"}
        }
    )
    
in
    RenamedFinal

Important architectural note: Notice that we're updating existing mart rows rather than appending new ones. This is an in-place reconciliation. If you need a full audit trail of what changed when — and in regulated industries like healthcare or finance, you absolutely do — you'll want to supplement this with a separate audit log table that captures the before/after values for every reconciled row. We'll cover the audit log pattern shortly.

---

Handling Truly New Late-Arriving Records (Not Updates to Existing Grains)

The scenario above assumed the mart already had a row for the grain being updated. But what if the late-arriving record belongs to a grain that has no existing mart row — for example, a product category that had zero on-time sales on a given date, but a late-arriving transaction fills it in?

You need an anti-join to find these orphan late arrivals:

// Step 6: Find late arrivals with no existing mart row (orphan late arrivals)
let
    // Left anti-join: late arrivals NOT in the existing mart
    OrphanLateArrivals = Table.Join(
        AggregatedLateArrivals,
        {"SalesRegion", "ProductCategory", "Date"},
        ExistingMart,
        {"SalesRegion", "ProductCategory", "Date"},
        JoinKind.LeftAnti
    ),
    
    // Shape into mart structure
    OrphanFormatted = Table.SelectColumns(
        Table.RenameColumns(
            Table.AddColumn(
                OrphanLateArrivals,
                "LastRefreshed",
                each DateTime.From(DateTimeZone.UtcNow()),
                type datetime
            ),
            {{"LateRevenue", "TotalRevenue"}, {"LateCount", "TransactionCount"}}
        ),
        {"SalesRegion", "ProductCategory", "Date", "TotalRevenue", "TransactionCount", "LastRefreshed"}
    )
    
in
    OrphanFormatted

Now combine the reconciled existing mart with the orphan new rows:

// Step 7: Combine reconciled mart with orphan late arrivals
let
    FullReconciledMart = Table.Combine({RenamedFinal, OrphanFormatted})
in
    FullReconciledMart

This is your complete reconciled mart — existing rows updated where late arrivals hit known grains, plus net-new rows for grains that had no prior representation.

---

The Audit Log Pattern: Recording What Changed and When

In any serious data environment, you can't just silently update historical numbers. You need an immutable record of what you knew, when you knew it, and what caused the change. This is especially true if your data feeds regulatory reports, financial statements, or SLAs.

Build a separate audit log query that captures every reconciliation event:

// Step 8: Generate audit log entries for every reconciled grain
let
    // Join original mart to reconciled mart to find what changed
    AuditBase = Table.NestedJoin(
        RenamedFinal,
        {"SalesRegion", "ProductCategory", "Date"},
        ExistingMart,
        {"SalesRegion", "ProductCategory", "Date"},
        "OriginalData",
        JoinKind.LeftOuter
    ),
    
    Expanded = Table.ExpandTableColumn(
        AuditBase,
        "OriginalData",
        {"TotalRevenue", "TransactionCount"},
        {"Original_TotalRevenue", "Original_TransactionCount"}
    ),
    
    // Only emit audit entries where something actually changed
    ChangedRows = Table.SelectRows(
        Expanded,
        each 
            ([TotalRevenue] <> [Original_TotalRevenue]) or
            ([TransactionCount] <> [Original_TransactionCount])
    ),
    
    AuditLog = Table.SelectColumns(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(
                    ChangedRows,
                    "RevenueImpact",
                    each [TotalRevenue] - (if [Original_TotalRevenue] = null then 0 else [Original_TotalRevenue]),
                    type number
                ),
                "CountImpact",
                each [TransactionCount] - (if [Original_TransactionCount] = null then 0 else [Original_TransactionCount]),
                Int32.Type
            ),
            "AuditTimestamp",
            each DateTimeZone.UtcNow(),
            type datetimezone
        ),
        {
            "SalesRegion", "ProductCategory", "Date",
            "Original_TotalRevenue", "TotalRevenue", "RevenueImpact",
            "Original_TransactionCount", "TransactionCount", "CountImpact",
            "AuditTimestamp"
        }
    )
    
in
    AuditLog

Tip: Load this audit log as a separate table in your data model and never overwrite it — only append. In Power BI, you can configure a table as append-only in Incremental Refresh settings. In dataflows, write audit entries to a separate SQL staging table using a stored procedure called from Power Automate.

---

Handling Slowly Changing Dimensions in Conjunction with Late Facts

Late-arriving facts become dramatically more complex when your dimensions are changing over time. Consider a customer who changed their assigned sales region from "Northeast" to "Southeast" on March 20th. A late-arriving sales transaction dated March 10th arrives on March 25th. Which region should own that transaction?

The answer depends on your business rules, but here's the technical mechanism for resolving it correctly using a Type 2 SCD lookup in M:

// Step 9: Resolve SCD Type 2 dimension values for late-arriving facts
let
    // Load your SCD2 customer dimension with effective date ranges
    CustomerDim = Sql.Database("your-server", "your-db")
        {[Schema="dbo", Item="DimCustomer"]}[Data],
    
    // This table has columns:
    // CustomerID, SalesRegion, EffectiveFrom, EffectiveTo (null = current)
    
    // For each late-arriving fact, join to the dimension version 
    // that was active on the fact's business date
    LateFactsWithDimKey = Table.AddColumn(
        LateArrivals,
        "CorrectSalesRegion",
        each 
            let
                factDate   = [TransactionDate],
                custID     = [CustomerID],
                
                // Filter dimension to the version active on the fact's business date
                ValidRows  = Table.SelectRows(
                    CustomerDim,
                    each 
                        [CustomerID] = custID and
                        [EffectiveFrom] <= factDate and
                        ([EffectiveTo] = null or [EffectiveTo] > factDate)
                ),
                
                // Return the region from the temporally correct version
                Region = if Table.IsEmpty(ValidRows) 
                         then "Unknown"
                         else ValidRows{0}[SalesRegion]
            in
                Region
        ,
        type text
    )
    
in
    LateFactsWithDimKey

Warning: This pattern — a row-by-row dimension lookup inside Table.AddColumn — is O(n × m) in complexity and will be painfully slow on large datasets because it cannot be pushed down to the source. For fact tables with more than ~100,000 rows, you'll want to do this join in SQL using a query folding-compatible approach, or pre-join in a staging view on the source side.

The performance-safe alternative: create a SQL view that pre-joins the fact table to the SCD2 dimension using a between-dates join, and reference that view from Power Query instead of doing the row-by-row lookup in M.

-- Create this view on your source database for query-fold performance
CREATE VIEW dbo.SalesTransactionsWithRegion AS
SELECT 
    t.TransactionID,
    t.TransactionDate,
    t.Amount,
    t.CreatedAt,
    t.ModifiedAt,
    c.SalesRegion  -- historically correct region for this fact's business date
FROM dbo.SalesTransactions t
JOIN dbo.DimCustomer c 
    ON  t.CustomerID    = c.CustomerID
    AND t.TransactionDate >= c.EffectiveFrom
    AND (c.EffectiveTo IS NULL OR t.TransactionDate < c.EffectiveTo)

Then reference this view directly in Power Query — it will fold the entire join to the source engine, and you avoid the M-level row iteration entirely.

---

Query Folding Considerations: Keeping the Engine Where It Belongs

Query folding is the mechanism by which Power Query pushes transformation logic back to the source system as native queries. When folding works, your incremental load happens at source-engine speed. When it breaks, Power Query drags the entire dataset into memory and processes it in the M engine — which for large fact tables is catastrophically slow.

Several patterns in this lesson can break folding. Here's a summary:

Pattern	Folds?	Notes
Table.SelectRows on simple column predicates	Yes	Becomes WHERE clause
Table.SelectRows using LastWatermark parameter	Yes (usually)	Depends on connector
Table.AddColumn with row-by-row M function	No	Forces in-memory evaluation
Table.NestedJoin between two M tables	No	Move this join to SQL
Table.Group after a fold break	No	Entire aggregation in M engine
Referencing another Power Query query in a join	No	Both datasets land in M memory

The practical implication: push as much of the reconciliation logic to SQL as possible. Use Power Query for orchestration, classification, and final shaping — not for set-based joining and aggregation when those operations involve large tables.

An architecture that performs well at scale:

1. SQL: Incremental pull, SCD2 join, initial aggregation to mart grain → exposed as a staging view
2. Power Query: Read staging view, classify arrivals, compute delta against existing mart, generate audit log
3. Power BI / Dataflow: Load final mart, refresh only changed partitions

This preserves the strengths of each layer rather than forcing M to do database-engine work.

---

The Snapshot Comparison Fallback: When Your Source Has No Arrival Timestamp

Some source systems — legacy ERPs, flat-file exports, third-party SaaS APIs — provide no arrival timestamp. Every export looks like a full snapshot. Late-arriving records are indistinguishable from any other record by metadata alone.

In this case, you must build arrival detection yourself by comparing consecutive snapshots. Here's the pattern:

// Snapshot comparison: detect new/changed/late records without arrival timestamps
let
    // Today's full snapshot from source
    TodaySnapshot = Csv.Document(
        File.Contents("\\fileserver\exports\sales_20240319.csv"),
        [Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    
    // Yesterday's snapshot, stored as a Power Query-accessible archive
    // (you maintain this archive as part of your pipeline)
    YesterdaySnapshot = Csv.Document(
        File.Contents("\\fileserver\exports\sales_20240318.csv"),
        [Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    
    // Promote headers, type columns
    TodayTyped = Table.TransformColumnTypes(
        Table.PromoteHeaders(TodaySnapshot),
        {
            {"TransactionID", Int64.Type},
            {"TransactionDate", type date},
            {"Amount", type number},
            {"CustomerID", Int64.Type},
            {"ProductCategory", type text},
            {"SalesRegion", type text}
        }
    ),
    
    YesterdayTyped = Table.TransformColumnTypes(
        Table.PromoteHeaders(YesterdaySnapshot),
        {
            {"TransactionID", Int64.Type},
            {"TransactionDate", type date},
            {"Amount", type number},
            {"CustomerID", Int64.Type},
            {"ProductCategory", type text},
            {"SalesRegion", type text}
        }
    ),
    
    // Add a hash of each row to detect changes (not just presence/absence)
    TodayHashed = Table.AddColumn(
        TodayTyped,
        "RowHash",
        each Text.Combine(
            List.Transform(
                {[TransactionID], [TransactionDate], [Amount], [CustomerID], [ProductCategory], [SalesRegion]},
                each Text.From(_)
            ),
            "|"
        ),
        type text
    ),
    
    YesterdayHashed = Table.AddColumn(
        YesterdayTyped,
        "RowHash",
        each Text.Combine(
            List.Transform(
                {[TransactionID], [TransactionDate], [Amount], [CustomerID], [ProductCategory], [SalesRegion]},
                each Text.From(_)
            ),
            "|"
        ),
        type text
    ),
    
    // Find records in today that weren't in yesterday (by TransactionID OR changed hash)
    TodayHashes     = Table.ToColumns(Table.SelectColumns(YesterdayHashed, {"TransactionID", "RowHash"})),
    YesterdayIDList = TodayHashes{0},
    YesterdayHashes_List = TodayHashes{1},
    
    // Net-new records: TransactionID not in yesterday at all
    NetNew = Table.SelectRows(
        TodayHashed,
        each not List.Contains(YesterdayIDList, [TransactionID])
    ),
    
    // Changed records: ID existed but hash changed (updated record)
    Changed = Table.SelectRows(
        TodayHashed,
        each 
            List.Contains(YesterdayIDList, [TransactionID]) and
            not List.Contains(YesterdayHashes_List, [RowHash])
    ),
    
    // Among net-new records, classify late-arriving by business date
    // (use a reasonable threshold — e.g., 3+ days old on arrival)
    TodayDate = Date.From(DateTime.LocalNow()),
    
    NetNewWithClassification = Table.AddColumn(
        NetNew,
        "ArrivalClassification",
        each 
            if Duration.Days(TodayDate - [TransactionDate]) > 3
            then "LateArriving"
            else "OnTime"
        ,
        type text
    ),
    
    // Combine changed and net-new for full delta
    AllChanges = Table.Combine({
        Table.AddColumn(Changed, "ArrivalClassification", each "Updated", type text),
        NetNewWithClassification
    })
    
in
    AllChanges

Performance note: The List.Contains approach on large lists is O(n) per row, making this O(n²) overall. For files larger than ~50,000 rows, convert your ID list to a Table and use Table.Join instead — it uses hash joins internally and performs dramatically better.

---

Handling Out-of-Order Records Within a Single Batch

Late arrival and out-of-order records are related but distinct problems. Late arrival means a record shows up in a later batch than it should have. Out-of-order means records within a single batch don't arrive in chronological sequence.

This matters when your pipeline processes records sequentially and uses position-dependent logic — running totals, lead/lag calculations, or session boundary detection.

The canonical M solution for imposing order on an unordered batch:

// Impose order and detect out-of-sequence records within a batch
let
    IncomingBatch = /* your incremental load result */,
    
    // Sort by business date first, then by arrival timestamp as tiebreaker
    Sorted = Table.Sort(
        IncomingBatch,
        {{"TransactionDate", Order.Ascending}, {"CreatedAt", Order.Ascending}}
    ),
    
    // Add a sequence number based on sorted position
    WithSequence = Table.AddIndexColumn(Sorted, "ProcessingSequence", 1, 1, Int64.Type),
    
    // Detect where the physical arrival order diverges from business-date order
    // A record is "out of order" if its CreatedAt is earlier than the previous 
    // record's CreatedAt (after sorting by business date)
    WithPrev = Table.AddColumn(
        WithSequence,
        "PrevCreatedAt",
        each 
            let
                seq = [ProcessingSequence],
                prevRows = Table.SelectRows(
                    WithSequence,
                    each [ProcessingSequence] = seq - 1
                )
            in
                if Table.IsEmpty(prevRows) then null
                else prevRows{0}[CreatedAt]
        ,
        type datetime
    ),
    
    WithOutOfOrderFlag = Table.AddColumn(
        WithPrev,
        "IsOutOfOrder",
        each 
            [PrevCreatedAt] <> null and 
            [CreatedAt] < [PrevCreatedAt]
        ,
        type logical
    )
    
in
    WithOutOfOrderFlag

Warning: The row-by-row lookback pattern above using Table.SelectRows inside Table.AddColumn is another O(n²) operation. For ordering and lag operations on large datasets, handle this in SQL using window functions (LAG(), ROW_NUMBER()) and expose the result as a view — Power Query is not the right engine for iterative lookback patterns.

---

Hands-On Exercise

Let's put this together in a realistic end-to-end scenario you can build yourself. You'll simulate a healthcare claims pipeline where claim adjustments routinely arrive 30–90 days after the original service date.

Setup: Create two CSV files to simulate your source.

claims_base.csv (represents records processed in a prior run):

ClaimID,ServiceDate,AdjustmentDate,PatientID,ProcedureCode,ClaimAmount,InsurancePayment,CreatedAt
1001,2024-02-01,2024-02-01,P-441,99213,350.00,280.00,2024-02-03
1002,2024-02-03,2024-02-03,P-872,93000,1200.00,960.00,2024-02-05
1003,2024-02-10,2024-02-10,P-331,99214,425.00,340.00,2024-02-12

claims_today.csv (represents today's source pull — includes late adjustments):

ClaimID,ServiceDate,AdjustmentDate,PatientID,ProcedureCode,ClaimAmount,InsurancePayment,CreatedAt
1001,2024-02-01,2024-02-01,P-441,99213,350.00,280.00,2024-02-03
1002,2024-02-03,2024-02-03,P-872,93000,1200.00,960.00,2024-02-05
1003,2024-02-10,2024-02-10,P-331,99214,425.00,340.00,2024-02-12
1004,2024-02-07,2024-03-15,P-441,99214,425.00,0.00,2024-03-15
1005,2024-02-14,2024-03-18,P-902,93010,890.00,712.00,2024-03-18

Claims 1004 and 1005 have a ServiceDate in February but a CreatedAt in mid-March — these are your late-arriving facts.

Exercise tasks:

1. Load both CSVs into Power Query. Implement the snapshot comparison logic from the fallback section to identify net-new records (claims 1004 and 1005).

2. Classify both net-new records using the ArrivalClassification logic. Both should be classified as LateArriving since their ServiceDate is more than 30 days before today.

3. Aggregate the late arrivals to the grain of (ServiceDate, ProcedureCode) summing ClaimAmount and InsurancePayment, counting claims.

4. Build a "prior-period summary" table by aggregating claims_base.csv to the same grain. Then perform a left-outer join and apply the reconciliation — adding late arrival amounts to any matching grain rows.

5. Add an audit log with OriginalClaimAmount, ReconciledClaimAmount, and ImpactAmount columns for every grain row that changed.

Expected outcome:

• The (2024-02-07, 99214) grain should be net-new (no matching row in the base summary)
• The reconciled mart should contain 4 grain rows total (3 from base + 1 from orphan late arrival)
• Claim 1005 introduces a new (2024-02-14, 93010) grain
• Your audit log should have 2 entries — one for each late-arriving claim

---

Common Mistakes & Troubleshooting

Mistake 1: Filtering on business date instead of arrival date for incremental loads

The symptom: late-arriving records never get picked up because WHERE TransactionDate > @watermark excludes records with old business dates that arrived recently.

The fix: always use ModifiedAt > @watermark (or CreatedAt > @watermark for insert-only sources) as your watermark filter. Business date is for classification, not for load filtering.

---

Mistake 2: Updating the watermark before the load completes successfully

If your pipeline updates the watermark timestamp immediately when a refresh starts, and then fails partway through, the next run will skip the records from the failed window.

The fix: only update the watermark as the last step of a successful load, and capture the maximum ModifiedAt from the loaded records — not the current clock time — as the new watermark. Records that arrive in the microseconds between your query and the watermark update will be caught by the next run's overlap.

---

Mistake 3: Assuming Table.NestedJoin between two large M tables is acceptable

Power Query will cheerfully execute a join between two 500,000-row tables in M memory, consuming gigabytes of RAM and timing out after 30 minutes. This looks identical to a fast query during development when you have 50 rows.

The fix: always check whether your joins fold to the source. Right-click the last step in your query and select "View Native Query." If you see a greyed-out option or an error, the step doesn't fold. Restructure the logic as a SQL view or stored procedure.

---

Mistake 4: Not accounting for deleted records

Late-arrival handling focuses on records that show up late. But what about records that exist in your mart from a prior period and get deleted from the source? (Voided transactions, fraud cancellations, etc.)

These will silently remain in your mart unless you implement a delete-detection pattern. For SQL sources, check for records in ExistingMart that are absent from the full source snapshot. For append-only sources, use soft deletes — a IsVoided flag — and propagate voids through your reconciliation logic.

---

Mistake 5: Mixing time zones without normalizing first

Your watermark might be stored in UTC. Your source system might record timestamps in Eastern Time. Your Power Query session might run in a server's local time. This creates invisible, intermittent gaps in your incremental load that appear and disappear depending on the time of day the refresh runs.

The fix: normalize everything to UTC at the point of ingestion. Use DateTimeZone.ToUtc() on all timestamps in your M code before any comparison, and store your watermark as UTC.

// Always normalize to UTC before comparing
NormalizedTimestamp = DateTimeZone.ToUtc(
    DateTimeZone.From([CreatedAt])
)

---

Mistake 6: Treating M's List.Contains as acceptable at scale

List.Contains on a list of n items runs in O(n) time per call. Called inside Table.AddColumn for each of m rows, this is O(n × m) — quadratic complexity. For small dev datasets it's invisible; at production volumes it makes refreshes hour-long.

The fix: convert your lookup list to a table and use Table.Join or Table.NestedJoin, which uses hash-based join algorithms internally. Even better: push the join to SQL.

---

Summary & Next Steps

You've covered a lot of ground. Let's consolidate the key architectural principles:

The dual-date principle: always track both the business date (when the event happened) and the arrival date (when the record entered your pipeline). These are different fields with different roles — filter incremental loads by arrival date, classify records by business date.

Watermark hygiene: store watermarks in a durable, external table. Update them after a successful load, using the maximum arrival timestamp from the loaded records — not the clock. This gives you idempotent, gap-free incremental loads.

The SCD2 temporal join problem: late-arriving facts must resolve against dimension values that were valid at the fact's business date, not the arrival date. If your dimensions change over time, you need SCD2 and a date-range join. Do this in SQL, not in M.

Query folding is not optional at scale: the moment your Power Query joins two large tables in M memory, your refresh becomes a time bomb. Audit every join and aggregation for foldability, and push non-folding operations to SQL views or stored procedures.

Audit logs are non-negotiable in regulated contexts: silent in-place updates to historical fact data are a compliance and trust problem. Build an immutable audit log that captures before/after values for every reconciliation event.

Where to go from here:

• Explore Power BI Incremental Refresh with Real-Time data — the platform-level incremental refresh feature integrates naturally with the watermark pattern you've built here, and understanding both together gives you a complete picture
• Study Slowly Changing Dimensions Type 3 and Type 4 — some late-arrival scenarios call for recording the prior value rather than overwriting, and these SCD types encode that directly in the dimension structure
• Learn dbt (data build tool) for the SQL layer of this architecture — if you're doing complex SCD2 joins and pre-aggregation in SQL before handing off to Power Query, dbt provides versioning, testing, and lineage tracking for those SQL transformations
• Investigate Azure Data Factory Data Flows if you're hitting Power Query's scaling ceiling — ADF Data Flows compile to Apache Spark and handle the kind of large-scale join and aggregation work that stresses Power Query
• Practice building operational monitoring dashboards on top of your audit log and arrival classification data — visualizing late-arrival rates, average lag days, and reconciliation impact by period turns this infrastructure into a business-visible quality metric

Late-arriving facts are one of those problems that reveal the maturity of a data engineering practice. Teams that ignore them produce reports that silently drift. Teams that handle them properly build systems stakeholders trust — even when the numbers change, because they can explain exactly why.