powerbi-time-series-data
Skill3 starsChanged 7 months agoMITView on GitHub
Time series analysis patterns. Use 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.
In this repository58 other files it gives its agents.
- powerbi-audit-tenant-settingsSkill
- powerbi-bpa-rulesSkill
- powerbi-connect-pbidSkill
- powerbi-connectSkill
- powerbi-create-pbi-reportSkill
- powerbi-c-sharp-scriptingSkill
- powerbi-custom-visualsSkill
- powerbi-cyber-securitySkill
- powerbi-data-catalog-lineageSkill
- powerbi-data-governance-traceabilitySkill
- powerbi-data-transformationSkill
- powerbi-dax-masterySkill
- powerbi-dax-performanceSkill
- powerbi-daxSkill
- powerbi-deneb-visualsSkill
- powerbi-doctorSkill
- powerbi-duckdb-lakehouseSkill
- powerbi-fabric-appsSkill
- powerbi-fabric-app-templatesSkill
- powerbi-fabric-capacitySkill
- powerbi-fabric-cliSkill
- powerbi-fabric-pipelinesSkill
- powerbi-fabricSkill
- powerbi-lineage-analysisSkill
- powerbi-measure-glossarySkill
- powerbi-medallion-architectureSkill
- powerbi-modelSkill
- powerbi-modifying-theme-jsonSkill
- powerbi-paginated-reportsSkill
- powerbi-pbi-cliSkill
- powerbi-pbip-formatSkill
- powerbi-pbir-cliSkill
- powerbi-pbir-format-enhancedSkill
- powerbi-performance-scaleSkill
- powerbi-power-querySkill
- powerbi-project-managementSkill
- powerbi-python-visualsSkill
- powerbi-refresh-semantic-modelSkill
- powerbi-report-authoringSkill
- powerbi-report-conversionSkill
- powerbi-report-designSkill
- powerbi-reportSkill
- powerbi-report-structureSkill
- powerbi-report-themingSkill
- powerbi-review-reportSkill
- powerbi-review-semantic-modelSkill
- powerbi-r-visualsSkill
- powerbi-security-rlsSkill
- powerbi-source-integrationSkill
- powerbi-spark-livySkill
- powerbi-standardize-naming-conventionsSkill
- powerbi-star-schema-modelingSkill
- powerbi-svg-visualsSkill
- powerbi-te2-cliSkill
- powerbi-te-cliSkill
- powerbi-te-docsSkill
- powerbi-testing-validationSkill
- powerbi-tmdlSkill
The file
Files
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
MergedWithDataStep 2: Binning to Rounded Intervals
Round timestamps to fixed intervals (Spark)
from pyspark.sql.functions import (
col, floor, from_unixtime, unix_timestamp, date_trunc
)
INTERVAL_MINUTES = 15 # Change to 5, 30, 60 as needed
df_binned = df.withColumn(
"time_bin",
from_unixtime(
floor(unix_timestamp("event_time") / (INTERVAL_MINUTES * 60)) * (INTERVAL_MINUTES * 60)
).cast("timestamp")
)Aggregate to binned intervals
df_agg = df_binned.groupBy("sensor_id", "time_bin").agg(
F.avg("temperature").alias("avg_temp"),
F.max("temperature").alias("max_temp"),
F.min("temperature").alias("min_temp"),
F.count("*").alias("reading_count"),
F.first("status").alias("first_status"),
F.last("status").alias("last_status"),
)Binning in DAX (dynamic bucket sizes)
-- Bin sales into $500 buckets
Sales Bucket =
VAR BucketSize = 500
VAR RawValue = [Total Sales per Customer]
RETURN
FLOOR(RawValue, BucketSize) & " – " & (FLOOR(RawValue, BucketSize) + BucketSize - 1)
-- Age binning
Age Group =
SWITCH(
TRUE(),
Customer[Age] < 18, "Under 18",
Customer[Age] < 25, "18–24",
Customer[Age] < 35, "25–34",
Customer[Age] < 45, "35–44",
Customer[Age] < 55, "45–54",
Customer[Age] < 65, "55–64",
"65+"
)Step 3: Fill Gaps — Create a Complete Time Spine
Full-resolution Date×Dimension spine (Spark)
# Cross join date spine with all sensor IDs to create a complete grid
date_spine = spark.range(0, 8760).select( # 8760 hours in a year
(F.to_timestamp("2024-01-01") + F.expr("INTERVAL id HOURS")).alias("hour_bin")
)
sensors = df.select("sensor_id").distinct()
spine = date_spine.crossJoin(sensors)
# Left join actual readings onto the spine
full_df = spine.join(df_hourly, on=["hour_bin", "sensor_id"], how="left")
# Forward-fill missing values (LOCF — Last Observation Carried Forward)
w = Window.partitionBy("sensor_id").orderBy("hour_bin").rowsBetween(Window.unboundedPreceding, 0)
full_df = full_df.withColumn("temp_filled", F.last("temperature", ignorenulls=True).over(w))Step 4: Normalisation
Z-Score normalisation (per sensor, per day)
from pyspark.sql.functions import stddev, mean
stats = df.groupBy("sensor_id").agg(
mean("value").alias("mu"),
stddev("value").alias("sigma")
)
df_norm = df.join(stats, "sensor_id") \
.withColumn("z_score", (col("value") - col("mu")) / col("sigma"))Min-Max normalisation to [0, 1]
from pyspark.sql.functions import min as spark_min, max as spark_max
bounds = df.groupBy("sensor_id").agg(
spark_min("value").alias("min_v"),
spark_max("value").alias("max_v")
)
df_scaled = df.join(bounds, "sensor_id") \
.withColumn("scaled", (col("value") - col("min_v")) / (col("max_v") - col("min_v")))CLI Commands
# Detect gaps in a Delta table time series column
pbi-agent fabric detect-gaps \
--table "bronze.iot_temperature" \
--time-col "event_time" \
--partition-col "sensor_id" \
--max-gap-seconds 300
# Bin a column and write aggregated table to Silver
pbi-agent fabric bin-time \
--table "bronze.iot_temperature" \
--time-col "event_time" \
--interval-minutes 15 \
--agg "avg:temperature,max:temperature,count:*" \
--output "silver.iot_temp_15min"Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

