Prevent data refresh error when column headers change or are removed

[grant_batchelor]

Hi everyone,

I wanted to share a scenario and see if anyone has encountered a similar issue or found a solution.

I need my data source to point to a folder where I save Excel files as I receive them. However, if a column header name changes or a column is removed in one of the Excel files, it causes an error during data refresh, such as "Column Name not found."

I found a workaround for column name changes by creating a list of column headers and associating a number with each column, as demonstrated in this video: Refresh breaks due to COLUMN NAME CHANGES? 3 ways to fix changing column headers in Power BI! - YouT.... Credit to "@HowtoPowerBI" for this method.

The problem arises when using a folder as the data source. New data from files added to the folder does not appear.

Has anyone experienced this issue and found a workaround?

Thanks!

[TomOs · Accepted solution]

I've run into a similar issue when working with dynamic Excel imports in Power BI. One approach is to use Power Query's Column Renaming Mapping to create a lookup table. You can maintain a reference table with expected column names and apply a transformation to match them during the import process.

For missing columns, you can modify the Transform Sample File step in Power Query and use Table.AddColumn to add placeholder columns when they’re missing. Another option is to Table.SelectColumns with MissingField.UseNull to allow Power BI to ignore missing columns rather than failing the refresh.

Since you're working with a folder source, make sure the Combine Files function is properly set up to handle schema variations. Also, if new files aren’t appearing, check whether the Transform File from Folder step is filtering out certain files due to unexpected metadata changes. You might need to review your applied filters and remove any that restrict data import.