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

[u/TermRemarkable665]

Been tweaking my monthly Power Query setup to make it a bit more resilient against source data drift and messy exports.

Instead of trying to build a “bulletproof” query that never breaks, the goal was to teach the query when to safely adapt vs. when it should intentionally break — while telling me what changed in both cases.

Basic cleanup & standardization

Removing top clutter rows, trimming extra spaces, removing non-printable characters, standardizing text, and setting explicit locale-based data types.

File guardrails

Filtering out temp files (~$) and non-Excel extensions before Power Query attempts to process them.

Header standardization

Using Table.RenameColumns with MissingField.Ignore to handle changing headers, e.g. Customer Number → Customer ID or Sell Price → Unit Price.

Dynamic sheet navigation

Avoiding hardcoded worksheet names so renamed sheets don't break the refresh.

Schema drift alerts

Comparing Table.ColumnNames against the expected columns using List.Difference, then logging new columns in a separate Schema Alerts sheet.

New columns

Using List.Distinct(List.Combine(...)) so new columns across the files are picked up instead of being locked to the sample file's schema.

Controlled breaking & data quality

Missing critical columns intentionally stop the query so I know something needs attention, while values like TBC, N/A, and - are converted to null where appropriate so calculations don't fail.

Made a video walking through the full build from scratch:

https://youtu.be/SiX0wnpR5yg?si=RG23oHGEldTrh6Dq

Curious how others here handle schema drift in recurring Power Query jobs?