Forum Discussion
moneerbarazi
Jan 22, 2020Copper Contributor
Expression error with data query
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 ...
PascalKTeam
Jan 23, 2020Iron Contributor
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
KPU73
Nov 24, 2020Copper Contributor
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 :))
- SergeiBaklanNov 24, 2020MVP
I'd suggest to check credentials in Data Source settings on this source.
Do you see your columns on Promote Headers step?
- KPU73Nov 24, 2020Copper ContributorThank 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?
- SergeiBaklanNov 24, 2020MVP
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.