Changing data source from "Enter Data" to Google Sheet source causes failures in refreshes

[zenisekd]

Hi,

I have a report, that takes its primary sales data from Postgres database. To get different currency sale values, I have added a fixed table via "Enter Data", merged it with sales table and everything worked fine. Now I wanted to change the source of the fixed table to Google sheet so I have created a new query, through which I connected the created sheet and then I have replaced via advanced editor the code of the original "Enter Data" source.

Suddenly however the refresh is having troubles. It drops in the desktop most of the time and the same happens in the Service. I have tried to clear cache, clear all permissions etc, but nothing works. Both are set to "Organizational level"...

Data source error: [ValidateMarkupTags][ccon]DataSource.Error: PostgreSQL: [ccon]Exception while writing to stream[/ccon]. DataSourceKind = [ccon]PostgreSQL[/ccon]. DataSourcePath = [ccon]icg-main-db-ro.rds.orgis.tools;odoo[/ccon]. Message = [ccon]Exception while writing to stream[/ccon]. ErrorCode = [ccon]-2147467259[/ccon]. [/ccon]. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: public account_analytic_line (timesheet).

Any suggestions?

[zenisekd · Accepted solution]

Just to give a little update, I did some testing around I found that if I keep the model with the static table and then I add a new table and repeat the steps to get the new currency value (not replacing the original one), the queries run smooth ... WTF ... seriously ... 

[PijushRoy]

Hi @zenisekd 

Not exactly sure, But you can try below steps
Take backup of PBIX file
Check any column name or datatype you have updated in Power Bi desktop (not Power Query)
Previous Json file column header and Google Sheet column header & datatype matching or not
Inplace of change the source, please replicate steps in Google Sheet query (copy from old query & paste in Google Drive query in advance editor)
Merge the Google Sheet with Sales table
Remove the old JOSN file.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[zenisekd]

@PijushRoy 

1. How do you check column name or datatype updated in Powr BI Desktop? There are no history steps, so I cannot see the changes...
2. How do I do: "Previous Json file column header and Google Sheet column header & datatype matching or not" ? What Json File are you talking about?
3. Just to be clear, I took the content of the original "Enter Data" source table and copy paste it to google sheets. Then I connected the google sheet and just used the "first row as a heaser. The data types and column names are the same. If there was a difference, I would get a clear error I could refer to and fix...

[PijushRoy]

Hi @zenisekd 

1. Check Power Query datatype and Power BI desktop Datatype name
2. When you entered manually data in Power Query, it will create as JSON file. It is same file I mean
3. Understood, inplace of change source of the file, try to create same step in Google Sheet query and then merge