Forum Discussion
Expression error with data query
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 :))
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, 2020Diamond Contributor
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.
- BillMcColloughSep 04, 2024Copper ContributorI 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.
- millerboyzNov 24, 2020Copper ContributorI 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
- SergeiBaklanNov 24, 2020Diamond Contributor
millerboyz Most probably Google, please check my previous post here.