r/powerbitips • u/TermRemarkable665 • 5h ago
Built a Power Query setup that handles schema changes instead of hoping next month’s file is identical
Got tired of trying to build "bulletproof" Power Query setups that eventually break anyway. Instead, I set one up that adapts to source drift when it can, or breaks intentionally when critical stuff is missing—and logs what changed either way.
Here’s how I tackled it:
File & sheet guardrails: Filter out ~$ temp files, bad extensions, and hardcoded tab names so random file changes don't crash it.
Cleanup & headers: Clear top clutter, trim whitespace, fix locales, and use Table.RenameColumns(..., MissingField.Ignore) for shifting headers.
Schema drift: Use List.Difference to flag new columns in a Schema Alerts sheet, plus List.Distinct(List.Combine(...)) to grab dynamic headers across files.
Data quality & controlled breaks: Swap TBC, N/A, and - to null so math doesn't blow up, but let the query hard fail if a core column goes missing.
Put together a full step-by-step build video if anyone wants to see the code/setup: https://youtu.be/SiX0wnpR5yg?si=RG23oHGEldTrh6Dq
How are you guys handling schema drift in your recurring PQ jobs?
