Expression error with data query

[moneerbarazi]

Hi, I have a table of data that is connected to my excel sheet through a query. When I try to refresh the table, I get this message:

[expression error]. The column "calendar" of the table wasn't found.

What do I do to fix this?

[#2]

Hi @moneerbarazi 

Depends...

Was the column removed on purpose? If not, add it to the source file again (in the same position it was before) and power query designer can load the data again

If the column was removed on purpose and you don't need it anymore, you must change all steps in the query designer which refer to this column. 

In my example, I have loaded data from 3 columns and then deleted column 2. Now I had to remove all steps in Power Query which were related to this column so basically I had to delete the text in red in screenshot below.

Hope this helps

[#3]

@PascalKTeam I am having a similar problem.

For over 4 years, we have been using a google sheet to get daily stock prices.  We then published that google sheet to a .csv file that we inserted as a new query in Excel.  It has been working swimmingly until 11/17/20.  Now we simply get the error that says [Expression.Error] The column 'Symbol' of the table wasn't found.  Nothing has changed in either the google sheet or the Excel file.  No column heading have changed.  In Excel, When I refresh the query - get the error - I went to Query, Edit, then clicked on Advanced Editor.  Here is what is says:

let
Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vRxo60CNgqdRzU2RrA-mwHVuXSIbBOg84uorEBwfr98QvDMVkHGTosxVTccn6zfRPAQ9wecQay8fgSY/pub?gid=1316400444&single=true&output=csv"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Symbol", type text}, {"Date", type date}, {"Curr Price", type number}, {"52 Wk High", type number}, {"52 Wk Low", type number}, {"Mkt Cap", type number}, {"# Shares Out", type number}, {"Volume", Int64.Type}, {"Change", type number}, {"% Change", type number}, {"Earnings/Share", type number}, {"P/E Ratio", type number}, {"Currency", type text}, {"Comments", type text}})
in
#"Changed Type"

This is what is in my Google sheet:

We have tried deleting the query from excel; republishing the Google Sheet to a new .CSV file and re-inserting the query in Excel - still the same error.

I'd be grateful for some specific help in how to fix this.  I'm pretty new to Power BI, so take some pity on how I'm explaining my issues please :))

[#4]

@KPU73 

I'd suggest to check credentials in Data Source settings on this source.

Do you see your columns on Promote Headers step?

[#5]

Thank you so much for replying. Unfortunately I don’t have as much knowledge on the subject as perhaps I should. I am learning. Do you know what steps I can take to check this? I suspect that this may indeed be the issue. This is part of a macro that runs every evening and when I checked the virtual machine that this was running on I noticed that it was asking me for my Google ID and password. It was trying to install Google Drive. I think something might’ve changed with Google? Anyway, how can I check these credentials? In Excel? Or do I do it somewhere on the Google sheet? I know how to get to the advanced properties of the query within excel, but where do I check the credentials?

[#6]

I am encountering the exact same issue! Stock quotes that I have used for several years has now stopped working. I've done nothing myself to the spreadsheet over the past week. Not sure if it is Google Drive or Excel

[#7]

@KPU73 

Please try again. It didn't work few hours ago, right now I repeated your Power Query again and it works

It looks like it was break in services.

[#8]

@millerboyz Most probably Google, please check my previous post here.

[#9]

While my Google file appears to update correctly each time it is accessed, the Excel file continues to produce the error message when I attempt to refresh with data from Google.

[#10]

@millerboyz I definitely think it had something to do with the link to the google sheet.  Now it works for me too.  Of course, I already crated a NEW google sheet and new spreadsheet to get it to work, but now when I go back to the old ones they work.  How crazy is that.  What gets me though is why it didn't just "say" that the problem was with some sort of connection/permissions issue, but instead gave the error alluding to a column issue.  Anyway - thanks to all who helped with this one.  As much as I'm a bit peeved at google right now, I couldn't do my job with out them, so will just have to chalk it up to a learning exprerience!

[#11]

@KPU73 

If there is the problem with permission or like, Google returns you not the document but some page which informs you can't access the file or like.

Excel has no idea about that, it takes the page returned by Google assuming that is your document, and tries to transform it into table. First error appears is the lack or Symbol column, that's how your script is built. It returns that error and stops.

You may handle such errors, but that will require more serious effort in Power Query script building with coding on M-script.

[#12]

I have the same problem, I can get it to show all of the data and even copy it to another page but when I try and get the data I can see to download it states permission issues. I have full access and I cant see any documentation in any of my books on Query that I have bought. (Im just learning it- love Excel and formulas but new to Query)

[#13]

I'm also flustered over the same problem. Google is constantly creating these updates from sheets that shouldn't be interfering with excel

[#14]

I am getting the same error in Excel, but it is due to the source data being downloaded (from web site data source).
Is there a way to ignore the error and complete downloading the rest of the data to the row?
I know it is the source as I am downloading the same fire weather data for specific times and only one time is generating the error. When I look at the .xml download it is returning data for the column that is erroring.
Thank you.

[#15]

@BillMcCollough 

I am getting the same error in Excel, but it is due to the source data being downloaded (from web site data source)

If the error is "[Expression error]. The column 'wxyz' of the table wasn't found" this means that somewhere in your current query code one (at least) step refers to a column named 'wxyz' that doesn't exist in the data Source (the Website you download from) at the time you Refresh the query

Is there a way to ignore the error and complete downloading the rest of the data to the row?

Maybe/Maybe not. Difficult to say without seeing your complete query code (post it if the following doesn't help)

When you create a query Power Query automatically creates a Change Type step after getting the data from an unstructured Source (i.e. a Website). That Change Type step "hard-codes" the column names (in the query code). For example, assume that when you create the query columns A, B, C, D exist in the data Source. Power Query auto. creates a Change Type step like:

= Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type text}, {"D", type number}})

If next time you Refresh the query column "C" doesn't exist in the data Source you'll get error "[Expression error]. The column 'C' of the table wasn't found"

Assuming a Change Type step exist in your current query edit the latter and delete that step...

[#16]

@Lorenzo 

Thank you.  I tried your suggestion of deleting the entire section and that worked.

[#17]

Glad this helped & Thanks for providing feedback - hope this will help others sooner or late
Nice day...