Data Refresh trouble - Excel on SharePoint (Refresh fails in both Service and Desktop)

[Michaela_Onu]

Hi everyone, and thanks in advance for any help. I have a set of reports built on Excel files that rest in SharePoint. I connect through Get Data - SharePoint Folder, find my SharePoint workspace and then choose the Excel file & tab that I need to use. To my recollection, the first few refreshes worked fine (I had Scheduled refresh set up), but this week the refresh does not pick up any changes in the Excel file. I have even tried refreshing through Power BI desktop, still no result - only when I went into Edit Queries (or Transform data) tab and "changed" the source manually again to the same file and same table again did I get the data refreshed, which is of course not feasible. 

Would you have any troubleshooting suggestions? I checked and the SharePoint files are updated and saved (and closed at the time of refresh), I have tried downgrading ApiVersion from 15 to 14, still no result.

[Anonymous]

Hi @Michaela_Onu ,

It there any changes apply to your datasource data structure? (column name, type and add/remove columns) Current power bi will cache table structure into the query table, if new records can't match with cache data structure, it can't be updated.

In addition, it sounds like refresh operation also failed on the desktop side. Can you please share more detail information about your scenario? (e.g. desktop version, error message, snapshot...)

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Michaela_Onu]

Hi Xiaoxin, 

thank you for getting back to me. 

I have not changed anything in the data source nor do I get an error message - Power BI acts as though everything has been refreshed correctly, but I do not see the new values picked up. When I compare the sums I get from the column in Excel and in Power BI, Power BI number is lower and outdated. 

Thanks, 
Michaela

[Anonymous · Accepted solution]

Hi @Michaela_Onu ,

It this excel file contains connections to external datasource? If this is a case, it seems like power bi refreshes only get current file data and not trigger the refresh in excel file.

I'd like to suggest you manually update your excel file and replace the published SharePoint excel file then refresh your pbix file to confirm if its refresh logic is similar as I mentioned.
Regards,

Xiaoxin Sheng