Report refreshes in desktop does not refresh in service

[Ricardo_MSS]

Hi All, 

I've been encountirng a strange refresh issue in Service. 

The report refreshes successfully in PBI Desktop but the same report when uploaded has failed to refresh 100% of the time. 

See error message below. 

After downloading the PBIX file to verify the issue the 'There are pending change in your queries that havne't been applied'. message is prompted. Even though this report has literally just been uploaded with all changes applied. 

After applying changes, refreshing in Desktop then re-uploading the process starts again.

No idea how to proceed. 

Any suggestions? 

[Ricardo_MSS]

@SLI @aj1973 @v-yalanwu-msft 

Ran into the solution by accident and as usual it was right under my nose. 

This morning when refreshing the report in desktop the error message from the PBI Service was reproduced. 

It turns out that the report was uploaded to a shared IT account workspace which did not have access to all the sharepoint source folders. 

When a filter was applied in query to select the correct Sample file from a specific folder ALL files were filtered out resulting in the previously mentioned error message. 

Once access was sorted in Sharepoint for the shared IT account the report refreshed both on desktop and Service. 

I am still confused why this happened just this morning and not before. My only guess is that somehow the credentials of my 'personal' work Microsoft account with which I log into my PC were somehow 'transfered' to PBI Desktop even when logged in to the shared IT account allowing for ocassional refreshes which failed today. 

Thank you all for your suggestions and help.

Best regads, 

Ricardo. 

[v-yalanwu-msft]

hi, @Ricardo_MSS ;

There are several points you may considerate:

1.Your data source credential may expire, so try to update the credential and republish the report. In Power BI, go to refresh settings for the dataset. In Manage Data Sources, select Edit credentials to update the credentials for the data source.

2.If your dataset not only contains cloud data source but also the on premise data source, then it needs to use the gateway and configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .

Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

3.Ensure that you've added one or more data sources of your dataset to the gateway, as described in Add a data source. 

4.Please make sure the gateway is online, and try to upgrade the gateway to latest version.

5. This error is thrown when pivoting columns with same name. Please check your column names in your data source. Also refer to a similar thread below:

https://social.technet.microsoft.com/Forums/en-US/7923e025-a0c4-407b-bd0f-65959e5038bb/what-does-thi...

 Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[aj1973]

Hi @Ricardo_MSS 

I would recommend updating the Gateway and see.

[Ricardo_MSS]

Hi Amine, 

Gateway renewed and still having the same issue. 

Still refreshes perfectly in Desktop version. 

Thank you for suggestion. 

Best regards, 

Ricardo. 

[aj1973]

@Ricardo_MSS 

Ok, It must be some pending changes that were not applied or were not correctly set in Power Query.

Open your report in the desktop and then open Power Query, look for something strange or not correctly set in the Query foldings for each of the tables. must be something wrong there

[Ricardo_MSS]

Hi @aj1973 , I've done as suggested but none of my queries have Query Folding in any of their steps.

All steps from all queries loaded without error in the QE.

The sources are:
- Excel and csv files stored in Sharepoint or on our local servers,

- a Salesforce dataflow (working perfectly well for other reports), and
- a connection to sql server which is also working well for other reports. 

Unless I am missing something I am back to square one. 

[aj1973]

Hi @Ricardo_MSS 

Are you saying that the sources of your report are all these?

Local servers? is there a Gateway there?

[Ricardo_MSS]

Hi @aj1973,
Thank you for the idea. 

It will be tried in the next couple of days and will let you know if it worked. 

Best regrads,  

Ricardo. 

[Ricardo_MSS]

Thank you for the suggestion, will try this out first. 

[selimovd]

Hey @Ricardo_MSS ,

there seems to happen the following error the query:

There were not enough elements in the enumeration to complete the operation

Try to figure out which query is causing that error and search in then more specific.

Maybe the following video can solve that already:

How to fix the "Too many elements in the enumeration to complete..." pivot error in Power Query - Yo...

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Ricardo_MSS]

Hi @selimovd, 

Thank you for your response and the video link. I've had to use that one in the past a couple of times 🙂

Shouldn't this error also stop my refresh in Desktop? 

To double check, I've gone through all the queries 1 by 1 and refreshed them both in Power Query Editor and in Report view and all the queries refreshed with no issues. 

I am back at square one. 😕 

Any other suggestions? 

[selimovd]

Hey @Ricardo_MSS ,

somehow there seems to be a difference between Desktop and the cloud.

If everything in Power BI Desktop works, try the following. Remove a table, save the file and try to refresh in Power BI Services. If the error still exists, continue with the next table, until you know which table is causing the error.

Once you found the table, let us know what exactly you're doing or try to find out by yourself.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Ricardo_MSS]

I'll be trying this out, thank you for the suggestion.