Schedule refresh isn't working with excel files on Sharepont

[Anonymous]

Hey guys, sorry for the long story below.

I've created a report using desktop and connected to several excel files Excel file in Sharepoint using Get Data->SharePoint Folder.
These files are the monthly plan for our assets. I've combined all of the files in one table in Power Query Editor. After connecting to the file with no errors, the data is correct. I can refresh the Excel data on desktop no problem. Then I upload the report to our organization workspace and all looks normal. There're two data sources in the report, one is to a SharePoint (on-premises) and the other is to the excel file in SharePoint.

I've set up the scheduled refresh including email to let me know if there're any failures. The problem is that schedule and on-demand refresh work with mixed success. Refresh works normally in 50% cases and crushed in other 50% with following error:

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"File contains corrupted data."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataFormat.Error"}}],"exceptionCulprit":1}}} Table: "Table_Name".

Where table name is the table that contains data from SharePoint Online. I've used OAuth2 with an Organizational privacy level to connect with SharePoint Online Dataset.

I've made some experiments with the aim of finding the reason for the problem and delete this table from Query Editor in Dekstop version and published report. After that everything works fine for more than 3 days. This is why I suppose that the problem in SharePoint Online Connection. 

Maybe someone could help find the answer to my questions:

1. Why does schedule/on-demand refresh sometimes crush on the web and everything always works fine in Desktop?

2. What should I do to fix the problem with SharePoint Online?  

[v-yingjl]

Hi @Anonymous ,

1. When you refresh in power bi desktop, it just refresh data on premiss. When you refresh in power bi service, you need to configure gateway
2. Refer to your error message, it seems like some wrong with gateway not SharePoint. Please try to check the gateway configuration and make sure that you've set the correct user with sufficient premission in the data source of your gateway. If the user is correct, try to delete and re-create the data source. If it still doesn't work, try to save your pbix in another name and publish it as a new report.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey, @v-yingjl 

"Please try to check the gateway configuration and make sure that you've set the correct user with sufficient premission in the data source of your gateway" - I've cheked everything. Credentials  are used in gateway are valid. (I suppose If credetials were invalid, report wouldn't refresh at all. However, our report refreshs succesefely sometimes. Besides, a couple oher reports with the same structure working with the same gateway refreshing succesully all time).

"If the user is correct, try to delete and re-create the data source" - I've made this. Unfortunatly, it didn't help.

"try to save your pbix in another name and publish it as a new report" - I've made this too. New report which I published has the same problem.

The strangest thing that I have 2 other report with the same data structure, working with the same gateway and this 2 reports refresh work oerfectly, the problem starts from the begining of the April.

For all of this 3 reports (for 3 our assets) we use 2 data sources: SharePoint on-prem (working perfectly) and SharePoint Online/One Drive with budget values. Budget Files are uploaded to Microsoft teams  every month.



In Query editor I connect to the SharePoint Onlne folder created by default for this Team of Microsoft Teams application.
In query editor we've combined all of this excel files in tne table.


And everything works fine escept one report and I couldn't understand "why?" 

[GilbertQ]

Hi there

It would appear from your error message that the data has changed and is causing an error from the Excel files.

Are the Excel files being edited during the day when the refreshes are happening?


What happens if you schedule a refresh overnight when no one is editing the files?

[Anonymous]

Hey @GilbertQ. Thanks for so quick reply.

Are the Excel files being edited during the day when the refreshes are happening?

I've checked that. The files were edited only by myself 2 days ago last time. Since that refreshes failed several times.
Besides, I have never refresh the report and edit these Excel files at the same time. 

What happens if you schedule a refresh overnight when no one is editing the files? 

Sometimes refreshes end successfully, sometimes they are failed. It doesn't matter if they were refreshing during the day or at night.

[GilbertQ]

Hi there

Can you check to see if you have got the latest version of the Gateway installed?

Also that the Gateway server has got good connectivity and is always up and running during the refresh?

[Anonymous]

Hey there! I've update the gateway to the last available version: 3000.31.3 (March 2020). Unfortunatly, this don't solve our problem. Shcedule/on-demand refresh still works with varying success.

[Anonymous]

Hey!)

Correct me if I wrong. Gateway installed on the server commonly used for refreshing other data sources, mostly on-premises.

I defenetly check it, but I could you please inglight me and explain how gateway affects on SP online data source?

(AfterI delete my table from power query and report, publishing it, everything works with my old gateway, but without data from SP online)