Column (it exists) not found from google sheet connections.

[msgrewal]

UPDATE: Now this error is being thrown for all google sheet connections in my Dashboard.

All first columns in any step after "Promoted Headers" step cannot be found, and error is being thrown for all google sheet related queries.

-------------

I have a query which was working fine. Data Source -> Connected to a google sheet. NO change in data source at all. No column change. No column added or deleted. And today, it started throwing error as below. 

--------

Interesting thing is that the Column which (supposedly) is not found is always the first column in command/step after "Promoted Header" actions. Meaning, it is not that a column is missing, it is that the "Promoted Headers" mess up columns in a way that the next applied step (whatever it may be, this case "Changed Type"), doesn't find the column it is looking for.

The error shows up under the step after Promoted Headers. Whatever that step is, be it "Changed Type" or be it "Choose Column". Anything. First column (In this case 'Execution date') in step after "Promoted Headers" is always the one on which error is thrown.

I am well aware of number of columns in "Source" step. Nothing has changed in that regard and I have already tried changing those number of columns in Source step and it does not impact this error.

[edhans · Accepted solution]

This thread is solved. The OP figured out that the export format from Google changed. No issues with Power Query.

[msgrewal]

@edhans 

@PhilipTreacy 

@Jimmy801 

Thanks for help.

Since this is a Source import issue, not 'Column not found' issue, I have reposted this issue here.

https://community.powerbi.com/t5/Power-Query/Googlesheet-CSV-connection-not-importing-properly/m-p/1...

Any help there is greatly appreciated!! 

[PhilipTreacy]

Hi @msgrewal 

Check that the column title Execution date doesn't have spaces before of after the words.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Jimmy801]

Hello @msgrewal 

if Power query says that there is no such column that the probability that there is no such column is very high. Check if the column name is exactly like that. Be aware that Power Query is case sensitive. So check if the column now is calles "Execution date" or if there are some spaces like "Execution Date "

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[msgrewal]

Hello Jimmy and Phil @PhilipTreacy 

I have 5 googlesheet (all different googlesheets) connections to my Dashboard. All of them stopped working (column not found error) overnight. In all, error is on the very 1st column used in applied steps. Please note no column or base data has changed in any of those 5 googlesheets. 

All other connections are working fine, except googlesheet connections.

Looks like problem is somewhere in the very first SOURCE import step.....

Refresh of SOURCE step of all googlesheets show the below, rather than standard CSV table as it has always shown. So if SOURCE step has problem, the very next applied step is not able to identify columns and throw errors

I have also re-verified all my googlesheet 'Publish to web' CSV output links. Nothing has changed literally in any of the published links.

[edhans]

@msgrewal a few suggestions:

First, get rid of the automatic changed type step. You can disable that in Options. It causes more problems than it solves as columns can change on import and the automatic Changed Type step will cause refresh errors.

Second, create a new connection to your source and look at the columns. I cannot see any "Execution Date" in the screenshots given that the Changed Type step is expected. Something is different, or you haven't posted the correct screenshots that shows "Execution Date" does in fact exist.

[msgrewal]

Hello Edhans, 

To your point, please see screenshots below.

All data and columns exits in data and they never changed. 

It doesnt matter if the Step is CHANGED TYPE or REMOVED COLUMN or any step......the very first step after source will throw column not found. 

SOURCE step should look like this:

But throws error in very first step after source:

and now SOURCE step is not showing table, it shows below HTML. Although nothing in source link to googleshet changed.

As mentioned, this error is only coming in on googlesheet connections. all other connections are fine. I have 5 googlesheet connections and all of them start giving this error on refresh. Nothing has changed in any of those 5 googlesheets.

If I delete all steps and just import data at SOURCE step, this is what I get. 

Not CSV as intended..