Power BI: Refresh success versus business freshness

Detailed practical guide

Prepare your workspace and inputs

Open a disposable Power BI file, choose Transform data, create a Blank Query and open Advanced Editor. Name the query FreshnessLab. The fixture has events at 11:00 and 11:30 UTC with an observation time of 12:00 UTC and a 15-minute freshness budget.

Platform context: Power BI Desktop with Power Query Editor; a new blank query. M code below uses a fixed UTC fixture, so the first result is independent of the time you run it.

Scenario to reproduce or investigate

A semantic model refresh completes successfully, but the latest business event is 30 minutes old. A successful connector run and a current business dataset must be displayed as separate facts.

1. Create the fixed source and freshness calculation

   Paste the complete M query. It returns a one-row summary containing the latest business event, observed time, event age and status. The observation time is intentionally fixed for a deterministic test.

   Command, configuration or input

   let
       Source = #table(type table [event_time = datetimezone, amount = number], {
           {#datetimezone(2026, 11, 4, 11, 0, 0, 0, 0), 100},
           {#datetimezone(2026, 11, 4, 11, 30, 0, 0, 0), 125}
       }),
       ObservedAt = #datetimezone(2026, 11, 4, 12, 0, 0, 0, 0),
       Latest = if Table.IsEmpty(Source) then null else List.Max(Source[event_time]),
       AgeMinutes = if Latest = null then null else Duration.TotalMinutes(ObservedAt - Latest),
       Status = if AgeMinutes = null then "NO DATA"
           else if AgeMinutes < 0 then "CLOCK OR TIMEZONE ERROR"
           else if AgeMinutes <= 15 then "FRESH" else "STALE",
       Result = Table.FromRecords({[latest_event_utc = Latest,
           observed_at_utc = ObservedAt, age_minutes = AgeMinutes, freshness = Status]})
   in
       Result

   Expected result · ผลที่ควรเห็น

   The query succeeds but freshness is STALE and age_minutes is 30. A green query refresh does not change that result.

   If it fails · เมื่อไม่ผ่าน

   If M reports a syntax error, check that the whole let...in expression was pasted. Keep event_time typed as datetimezone; do not subtract a local unzoned datetime from UTC.

2. Test fresh, empty and future-clock cases

   Change only the second event from 11:30 to 11:50 and refresh the preview. Then test an empty fixture by replacing its rows with an empty list. Finally restore the rows and change the latest event to 12:05. Record all four observed outcomes before connecting a live source.

   Command, configuration or input

   11:30 latest → age 30 → STALE
   11:50 latest → age 10 → FRESH
   No rows → null age → NO DATA
   12:05 latest → age -5 → CLOCK OR TIMEZONE ERROR

   Expected result · ผลที่ควรเห็น

   All four outputs agree with the stated conditions. Empty input is not labeled fresh and a future timestamp is not silently accepted.

   If it fails · เมื่อไม่ผ่าน

   If empty input fails at List.Max, verify that the Table.IsEmpty guard remains before the maximum calculation. If dates shift, inspect the source timezone conversion instead of increasing the freshness budget.

3. Connect the indicator to the business source

   Duplicate the lab query. Replace only Source with the approved business query that exposes a non-null UTC event_time and replace ObservedAt with DateTimeZone.FixedUtcNow(). Push the watermark aggregation to the source where appropriate rather than loading a large fact table just for this indicator. Display event freshness beside the source load watermark and semantic-model refresh completion.
   
   A maximum timestamp alone does not prove that earlier expected records are present.

   Command, configuration or input

   ObservedAt = DateTimeZone.FixedUtcNow()

   Expected result · ผลที่ควรเห็น

   The report distinguishes connector completion, upstream publish progress and business event age for the same documented timezone and dataset.

   If it fails · เมื่อไม่ผ่าน

   If the source legitimately stops producing outside business hours, define a business-calendar freshness budget with the data owner. Do not mask missing events by changing the observed timestamp.

Recovery and cleanup

Keep the lab query separate and retain the original report file before adding the live indicator. Restore the prior query if the new source aggregation changes folding, load or report correctness.

Apply to actual work and known limits

The M fixture has been checked against the documented functions, but Power BI execution must be verified in your Desktop and service environment. Do not treat a latest-row indicator as proof of completeness across every partition or business key.

Authored against the cited documentation. Execution on the stated vendor product or physical equipment remains to be verified.