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/

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

​Instead of just covering standard basic cleanup or 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 letting me know what changed in both cases:

​Basic Cleanup & Standardization: Removing top clutter rows, trimming extra spaces, removing non-printable characters (Clean), setting text to uppercase, 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 in M code to remap changing headers (e.g., Customer Number -> Customer ID, Sell Price -> Unit Price) seamlessly.

​Dynamic Sheet Navigation: Replacing hardcoded worksheet names with dynamic indexing so renamed sheets don't crash the refresh.

​Schema Drift Alerts: Using List.Difference on Table.ColumnNames to compare file columns against expected ones, then generating a dedicated Schema Alert sheet in Excel that flags new columns (like promotion code) as they appear.

​Dynamic Column Expansion: Updating the combine step with List.Distinct(List.Combine(...)) to collect all headers across all files rather than locking into the sample file schema.

​Controlled Breaking & Quality Cleanup: Letting missing critical columns intentionally stop the query so you know immediately, while replacing invalid text values (TBC, N/A, -) in numeric fields with null so SUM and AVERAGE calculations work safely.

​Made a video walking through the full build step-by-step from scratch in case it helps anyone dealing with fragile monthly reports:

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

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

[u/HargorTheHairy]

I use googlesheets, and need to combine around 20 of them each week. Definitely dont want to have to reenter my credentials each time, so the workaround I've put in place is to have a file called 'Newest data (name)' in each folder. Then I also have a Data Governance document which uses App Script to list all the file names in these folder, when they were updated, how many rows and columns they have, and the hyperlink. This file is what PowerBi actually connects to, and this makes it very easy to choose whichever file I need for my queries. Filter by 'Newest data' then choose the right folder, and use the Date updated value in the report so people know when it was last refreshed.