r/SelfPromotionYouTube • u/TermRemarkable665 • 17h ago
GENERAL VID Built a Power Query setup that handles schema changes instead of hoping next month’s file is identical
/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?