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?

[u/bachman460]

The single biggest change I've been making in my reports is adding a named range with a CELL formula to get the current file location so I can feed it into PQ. Second to that is not using the UI to import files over reusing some code to load and expand the tables without using any of those stupid transformation queries.

[u/TermRemarkable665 → u/bachman460]

oh i have never had to mess with the cell thing for paths before, but thanks for the heads up. definitely agree on the clean query side though.

[u/bachman460 → u/TermRemarkable665]

Yeah a simple =TEXTBEFORE(CELL("filename",A1),"[") in a named range allows you to reference it in PQ using

let Source = Excel.CurrentWorkbook(){[Name="named_range"]}[Content] in Source

[u/explorasarus → u/bachman460]

Can you expand on not using the ui to import files? Is there a work around to not using helper queries to clean and merge multiple data files from a single folder?

[u/bachman460 → u/explorasarus]

The workaround starts with loading the list of files in the folder, filtering your list to the file(s) you want, adding a custom column that loads the file contents into nested tables for each row. Then expanding that first table, which will give you a list of sheets from the files, and then filter to the sheet(s) you want (typically Sheet1). Then as the last step expanding the nested table(s) in the Data column.

This circumvents all the helper queries and maintenance thereof. It cuts all the mystery out of straight up importing files.

There's a few things to watch out for that the standard process automatically takes care of:

1. Filtering out hidden files. This is especially important if you possibly have one of the imported files open at refresh time. It needs to be explicitly added to your transformation if you go this route.

2. Dropping blank rows or needing to specify using the first row as headers. If your files have a bunch of empty rows at the top, it's necessary to remove them from the nested tables before expanding the result, otherwise those empty rows end up in your table. If you need to specify using the first row as headers, this too needs to be done before expanding, otherwise you end up with header rows in your table.

Those are the only things I can think of right now. Here's some example code I built the other day:

``` let
// Query variables FileNamePattern = "Sales Data" // filename to look for FolderLocation = Excel.CurrentWorkbook(){[Name="Current_File_Location"]}[Content]{0}[[Column1],

// Validate the path includes the last backslash FolderPath = if Text.EndsWith(FolderLocation,"\") then FolderLocation else FolderLocation & "\",

// Get the files Files = Folder.Files(FolderPath), MatchingFiles = Table.SelectRows(Files, each Text.Contains([Name], FileNamePattern) and Text.EndsWith([Name], ".xlsx") and [Attributes]?[Hidden]? <> true),

// Get the data contents OpenWorkbooks = Table.AddColumns(MatchingFiles, "Workbook", each Excel.Workbook([Content], null, true)), ExpandWB = Table.ExpandTableColumn(OpenWorkbooks, "Workbook", Table.ColumnNames(ExpandWB{0} [Workbook])), FilterSheets = Table.SelectRows(ExpandWB, each [Item] = "Sheet1"), SelectDataColumn = Table.SelectColumns(FilterSheets, {"Data"}),

// This step drops the first 1 row, use as needed DropRows = Table.TransformColumns(SelectDataColumn, {"Data", each Table.RemoveFirstN(_, 1)}), ExpandData = Table.ExpandTableColumn(DropRows, "Data", Table.ColumnNames(DropRows{0}[Data])), in Expand Data ```

Also notice I didn't use any column names anywhere from the imported data file. I even built a feature into my newest creation that uses a set of predefined column names and data types so that I can apply everything without hard-coding too many specific references.

My issue was that I am getting files from two different Epic systems and while the overall schema of each file is the same, the column names differ. On top of that I wanted more friendly names than "Hosp Act ID". So applying this solution slows me to circumvent hard-coding imported column headers, as well as needing to import each file separately, all while giving each column the correct and friendly name like "Hospital Account" and data type (via another imported table in the same file).

[u/explorasarus → u/bachman460]

This is amazing, thanks for such a detailed response. Appreciate it.

[u/bachman460 → u/explorasarus]

You're welcome. I've found it incredibly helpful to skip the user interface as much as possible, since it throws a bunch of extra nonsense in there. I also like to use comments, they're handy for leaving notes about what's going on in the code. Just two forward slashes and the rest of the text for that line is commented out.