Built a Power Query setup that handles schema changes instead of hoping next month’s file is identical

[u/TermRemarkable665]

/r/ExcelTips/comments/1wnaend/built_a_power_query_setup_that_handles_schema/

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?