Power Query Error when importing Excel files after formatting a table area in excel

[synaptical]

Hi,

I have a power bi file that reads several excel files from a folder. In the excel files, we have several columns, some are optional to use and might be empty if not needed (except row 1 which is the header). In this excel templates, we experiencend problems when inserting new rows, as one has to copy all the formulas. Therefore, I selected our area and formatted it to a table in excel.

Now when trying to import data, power bi shows an error saying column_1 has not been found. In Excel, this column is named "Title".

I tried to search for a solution but all I got was I need to adjust my power query in Power BI.

Is there an easy or easier way to solve this problem - in Power BI or even in Excel? Data transformation took me a long time and recreating everything would cause a lot of effort.

Thanks for any help on this in advance!

[Poojara_D12 · Accepted solution]

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.

[v-pgoloju]

Hi @synaptical.,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @synaptical,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @hnguy71 and @danextian  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[danextian]

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.

[synaptical]

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?

[v-pgoloju]

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

[hnguy71]

Hi @synaptical 

There is a way to return all columns (both mandatory and optional) and then be able to import them all together. It does require a small amount of PowerQuery magic but it's not impossible. 

Do you have some sample data?

EDIT: Here's a sample of how to bring in all columns. I've included comments for you to understand what's going on. For example:

[synaptical]

Hi

Thank you very much for your support.

I hope I get your solution approach: I have to insert a step in Power Query named "CustomExpand".

I tried it and Power BI says "The column 'Transform File' of the table wasn't found." I adusted the query as follows:

= Table.ExpandTableColumn( #"Removed Other Columns1", "Transform File", Table.ColumnNames(Table.Combine( #"Removed Other Columns1"[#"Transform File"])) )

Unfortunately, I have no proper sample data as my sources are classified to be used whithin the company.

Please consider the import of several file worked fine before I did this: It broke when I adjusted in excel (selected columns and rows and marked them as a table). And I am currently testing with one excel in the data source folder, not several as usual.

Could you help me anyway?

[hnguy71]

Hi @synaptical 

There are two areas in which you need to do the changes.

1. From your "Transform Sample File" default helper query. Make sure you removed the change data type step from there:
   
2. The "Transform File" is a column reference. while "Removed Other Columns1" refences my previous applied step. Without seeing your code I'm not sure where you've placed it.

Can you open up the advanced editor and copy and paste the entire query here?