Expression.Error: The Column 'Column X' of the Table wasn't Found

[mrbuttons07]

Hello everyone,

I established a connection from an excel workbook to a master excel workbook. I received the error as above stating that the the table was not found. The issue here is that the table does appear and is needed in both excel workbooks. 

I notice that this issue appears when I open both files together at the same time. Also, when I update any information in the workbook, the error appears as well.

I made some research about this issue and all goes to the same solution, which is to remove the error column. It doesn't work as it prompts another error stating that 'Column Y' was not found. Let me know if there are any other solutions that I can try with. Thanks again!

[Jimmy801 · Accepted solution]

Hello @mrbuttons07 

in my experience this is coming from a hardcorded part of your query. Maybe the datasource changed (most probably) and therefore your query is running into this issue (example: power query reads the usedrange of a sheet. So maybe at a startingpoint the usedrange was very big - even without data. You registered your query without thinking about this isse. Then somebody fixed the Excel-file and now you have problems with the query.)

Hope this helps

Jimmy

[mrbuttons07]

You are right, Jimmy. The data source that I have changes frequently time to time and therefore the issue appears. I made a quick investigation based upon the data source. 

Thanks for your feedback, Jimmy.

[Jimmy801]

Hello @mrbuttons07 

thanks for the feedback.

So the only way to get around such an issue is to write your queries dynamically, and due that prevent any hardcoded columnnames. It's not always possible, but for sure you are able to handle a lot of issues with that.

BR

Jimmy

[rajulshah]

Hello @mrbuttons07,

You can move to 'Source' step in Query Editor and see which columns are present and which of them are absent.

You can thus see which columns exist in Excel.