Refresh Completing But Data Missing

[Anonymous]

Hi,

I'm running into a peculiar intermittent issue when refreshing an Excel spreadsheet in the Power BI Service. For context, I have an Excel Spreadsheet in a Sharepoint site that is set to refresh three times a day, it uses Salesforce Objects as a Data Source, and returns roughly 60,000 rows. I've had this spreadsheet running for a few weeks without any issues (at least none I've noticed), until this morning.

I had a user report that some of the records in SFDC had been updated two days ago, but these changes hadn't been reflected in the spreadsheet. I looked into it and there hadn't been any changes made to the spreadsheet at all in the 3 days prior. Having checked the Refresh History, all of the "Scheduled" refreshes had completed successfully (taking around 90 minutes) and the "OneDrive" refreshes had also all completed successfully. Yet when I looked at the version history of the file, the size of the file remained exactly the same for each version of the file over the 3 days, usually I would expect some small variance here.

Peculiarly, the refresh this morning actually contained refreshed data, yet I hadn't changed anything. I don't use a Gateway for this refresh and Query Caching is turned off. The most concerning thing for me is that there is no record of anything failing, yet something has to have gone wrong.

If anyone has any pointers, has experienced something similar or knows of something else to check then that would be much appreciated!

Cheers,

Tom

[QueryWhiz]

Hi, were you able to find a solution? I think in this case would be better for you to go directly from your SF to PBI, that way you won't be worrying wether your spreadsheet refreshed or not. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics (which doesn't have PBI as a destination, so you first have to export to Google Sheets and then export to PBI) and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

 There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[v-shex-msft]

Hi @Anonymous,

So you mean your data was stored in excel which a connection to the external data source, right?
AFAIK, power bi refresh will check your data source records. So if your records not updated in your file, power bi refresh will be triggered normally and not update any records.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

So the data source is SFDC, I used the "Get Data" functionality to upload an Excel file that has Power Query queries within it that load the dataset into the sheet so that users can download copies (the file is set to read only for all but myself). I then set up a Scheduled Refresh for the Excel file so that the data in there is updated 3 times a day. The records that were being pulled from SFDC had definitely been updated, I can see a history of those records from the front end of Salesforce.

My concern is that something failed in the refresh causing the data in the sheet not to be updated, but no failure was recorded in the Refresh Histories.

Thanks,

Tom

[v-shex-msft]

Hi @Anonymous,

Power query data connection in excel file not auto-update, it is a local file that contains a data link to source data and not able to update itself. You need manually to open this file and do refresh operation to get the latest data or try to configure the data connection to 'Automatically refresh data at regular intervals' mode.

Refresh an external data connection in Excel 

For power bi refresh operations, it checks file contents and not trigger the internal data connection to get new records.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Please see the bottom of this page: https://docs.microsoft.com/en-us/power-bi/service-excel-workbook-files

"Schedule refresh - If your Excel workbook file connects to external data sources, or you imported from a local drive, you can setup scheduled refresh to make sure your dataset or report is always up-to-date. "

And subsequently: https://docs.microsoft.com/en-us/power-bi/refresh-data

"If you created your datasets and reports based on a Power BI Desktop file, Excel workbook, or comma separated value (.csv) file on OneDrive or SharePoint Online, Power BI performs another type of refresh, known as OneDrive refresh."

Using the Get Data functionality and then the "Upload your Excel file to Power BI" option, it is possible to create a spreadsheet that will update itself once it is connected to the Power BI Service.

Regardless of the above, in my inital post, I commented that I had seen this functionality working prior to my issue and subsequently it has been working. My concern was that there was a period where a Scheduled Refresh and a OneDrive Refresh both said that they had "Completed" with no errors, when the data had not been updated.

Tom

[v-shex-msft]

Hi @Anonymous,

So you mean you are work with an excel workbook that deploys on power bi service side with an external data connection to other data sources, right?
If all refresh log and history works well but not record updated, I think it may cause by backend verification or transforms, perhaps you can submit an idea to ask power bi team trace these.

submit a support ticket

Regards,

Xiaoxin Sheng