powerbi-time-series-data

Skill: Time Series Data — Consolidation, Gaps, Normalisation & Binning

Trigger

Activate when the user mentions: time series, gaps in data, missing dates, sparse data, irregular intervals, binning, time buckets, rounding timestamps, 5-minute intervals, hourly buckets, normalise time, fill gaps, spine, date spine, continuous axis, aggregation over time, interval aggregation, IoT, sensor data, event data, tick data

What You Know

You have handled time series at every scale — from financial tick data (microsecond resolution) to IoT sensor streams (millions of events per day) to monthly business KPIs. You know how to identify gaps, normalise irregular timestamps to clean intervals, and build the aggregation patterns Power BI needs to work correctly.

Step 1: Identify Gaps in Time Series

In Spark (Silver layer)

from pyspark.sql.functions import col, lag, datediff, unix_timestamp
from pyspark.sql.window import Window

w = Window.partitionBy("sensor_id").orderBy("event_time")

df_gaps = df \
    .withColumn("prev_time", lag("event_time").over(w)) \
    .withColumn("gap_seconds",
        unix_timestamp("event_time") - unix_timestamp("prev_time")) \
    .filter(col("gap_seconds") > 300)  # flag gaps > 5 minutes

df_gaps.select("sensor_id", "prev_time", "event_time", "gap_seconds").show()

In DAX (Report layer — detect missing date rows)

-- Count days with zero transactions (gaps in your date table)
Days With No Sales =
CALCULATE(
    COUNTROWS(dim_date),
    FILTER(
        dim_date,
        CALCULATE([Total Sales]) = 0 || ISBLANK(CALCULATE([Total Sales]))
    )
)

-- Flag a gap period in a line chart (show as dashed or zero)
Sales Filled =
IF(ISBLANK([Total Sales]), 0, [Total Sales])

In Power Query / M

// Generate a complete date spine (no gaps) and left-join your data onto it
let
    StartDate = #date(2020, 1, 1),
    EndDate = Date.From(DateTime.LocalNow()),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    MergedWithData = Table.NestedJoin(DateTable, "Date", YourData, "TransactionDate", "Data", JoinKind.LeftOuter)
in
    MergedWithData