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.
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.
Comments
0 real commentsNo real reader comments yet.
✎ Write a comment