Forum Discussion
Power Query Error when importing Excel files after formatting a table area in excel
Hi synaptical
This issue occurs because when you converted your Excel range into a formatted table, Power BI no longer recognized the old column structure it was originally mapped to. In your query, Power BI is still looking for a placeholder field like Column1, but now the actual table header is Title, which causes the error. You don’t need to rebuild your whole transformation; instead, open the Power Query Editor and adjust the step where the error appears (usually “Promoted Headers” or “Changed Type”) by replacing Column1 with Title. If you are combining multiple files from a folder, make sure the sample file query is corrected, as Power BI applies those steps to all files. Going forward, keep header names consistent in your Excel templates, and in Power Query use the “Choose Columns” step with the dynamic option enabled so that missing optional columns don’t break refreshes. This way, you only fix the column reference once, and all future files will load smoothly without recreating your transformations.
Hi synaptical
Did you connect through the folder connector? Expanding tables and including a column that doesn’t exist normally isn’t an issue—it just produces a column with null values. The error usually occurs when a non-existent column is directly referenced in other transformations, such as changing data types, renaming, removing, or filtering.
Thank you for the input. Yes, I connect via the Folder connector. I think I found where the problem lies: As I formatted my excel sheet now as a table, I found that I had two columns in excel which were "Quantity" so excel renamed them to quantity1 and quantity2.
So all columns but just one now were renamed in excel as they were before, except one column has a changed name. Means I would not have to do all the data transformation steps but just fix this one. Do you know a resource on how to solve this best?
Hi synaptical,
The issue happens because Power BI remembers column names exactly as they were when you first connected. Since Excel renamed one of your duplicate “Quantity” columns to “Quantity1” or “Quantity2,” Power BI can’t find the old name anymore. The easiest fix is to open Power Query, find the step where the error appears, and rename that new column back to “Quantity” (or adjust the step to use the new name).
Thanks & REgards,
Prasanna Kumar