Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Refresh Completing But Data Missing

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

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

  • QueryWhiz's avatar
    QueryWhiz
    Post Patron
    2 years ago

    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. 

     

  • metrica's avatar
    metrica
    Post Prodigy
    1 month ago

    Hi Tom,

     

    This looks like a refresh-chain issue rather than a simple Salesforce issue.

    You have Salesforce data going into Excel through Power Query, the Excel file stored in SharePoint, and then Power BI Service refreshing that workbook. In that setup, a successful Power BI or OneDrive refresh does not always prove that the internal Excel Power Query connection actually reloaded fresh Salesforce data and wrote it back into the workbook.

     

    I would check:

    • Whether the Salesforce query inside Excel is configured to refresh reliably.

    • Whether the updated rows are loaded into the workbook data model or only into a sheet/query.

    • Whether the SharePoint file version actually changes after the Salesforce refresh.

    • Whether Power BI Service refresh history is showing data refresh, OneDrive refresh, or only file sync.

    • Whether you can simplify the architecture and avoid Excel as the middle layer.

     

    If the goal is Salesforce to Power BI reporting, I would test a direct Salesforce to Power BI path instead.

     

    One option is Power BI Connector for Salesforce by Metrica Software, available on Salesforce AgentExchange / AppExchange: https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577

     

    With this approach, you create the data source inside Salesforce, select the objects and fields you need, apply filters, and connect Power BI to that prepared source directly. This removes the extra Excel and SharePoint refresh layer from the pipeline.

     

    A few relevant points:

    • Supports standard and custom Salesforce objects.

    • Lets you select only required fields and apply filters before data reaches Power BI.

    • Helps avoid spreadsheet-based refresh chains.

    • If you also use Salesforce Reports, it avoids the frustrating 2,000-row Salesforce Reports limit.

    • There is a 30-day free trial.

     

    Docs: https://metricasoftware.com/docs/salesforce/

    Support: https://metricasoftware.com/docs/salesforce/contact-support/

     

    Disclosure: I am with Metrica. If helpful, we can also set up a quick demo and walk through the Salesforce data source setup, Power BI connection, and refresh behavior.

     

    Hope this helps if the main problem is that the refresh completes but the Salesforce data in the workbook is still stale.

     

    Cheers,

    Metrica Team.