TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
Power Query schema drift: a successful refresh can still be wrong
Business Intelligence · 2024Photo: Unsplash / Unsplash ↗
Business Intelligence2024 / RETROSPECTIVE

Power Query schema drift: a successful refresh can still be wrong

Managing source-schema changes, explicit types and data-quality checks in Power Query pipelines.

Yazılımcının GünlüğüPublished: Nov 16, 20263 min read

Sample period date: Jan 10, 2024. These essays revisit earlier technologies from today's editorial perspective; they are not represented as posts originally published in those years.

A successful refresh is not proof of correct data

An Excel source may change monthly. Columns are renamed, date formats differ and values arrive with unexpected types. Power Query can finish without raising an error while the business total becomes incorrect. Separate refresh success from output correctness by checking row counts, missing keys and date ranges.

Treat the source schema as a contract

Document required columns and their types. Decide whether unexpected new fields should be ignored or explicitly incorporated. Selecting columns by position is fragile; prefer stable field names where possible. Yet silent tolerance to renamed columns can also cause bad mappings, so test schema changes deliberately.

Types and locale matter

Decimal separators, date formats and blank cells can behave differently across locales. Apply explicit data types and locale-aware conversions. Instead of silently dropping invalid rows, collect them in a separate error output. Concealing bad source records may make the dashboard look cleaner while making its numbers less trustworthy.

Run regression checks before publication

Process samples from the previous and current months through the same query. Compare key uniqueness, matched records, totals and null rates. Define expected ranges that distinguish legitimate business changes from pipeline failures. A green refresh status should never be the only release criterion.

0 reads

Comments

0 real comments

No real reader comments yet.

✎ Write a comment

Comments are reviewed before publication.