Power BI reports not updated with latest SharePoint data

[anna_c]

Hello, 

I am new to Power BI and am having trouble getting data from a SharePoint file to update when I refresh my Power BI report. 

I have a report in Power BI and one of the data sources is an excel file saved on SharePoint. In the SharePoint file, there is a lookup based on today's date, so whenever you go into the file, the data shown is relevant to today. 
When I refresh my Power BI report each day, it doesn't show the latest SharePoint data. There is no error, everything appears to refresh without issues but I can see that the values have not been updated.
The only way I have been able to get the data to update is to go into the SharePoint file first and edit a cell (I don't actually change anything just click in and out again). After doing this if I refresh Power BI the up to date data is now shown. I imagine editing the Sharepoint file gives it some sort of "kick" to register that a change has been made the file.

Is there a way to get this to update without having to manually go into the SharePoint file each day? Or is there a better solution to achieve what I need?

Thanks in advance, 
Anna 

[Burningsuit · Accepted solution]

Hi @anna_c 

This is down to the way Excel works, "Today()" gets evaluated when the Sptreadheet is calculated, that is, when you open the Spreadsheet, or press f9 to force a re-calculation etc. Loading a file into Power BI doesn't make the Spreadsheet re-calculate, so you get whatever was in there the last time you saved.

Other than daily going into the Excel file to update Today(), you've got a couple of options.

Remove the Today() filter from the Spreadsheet, and load all the data into Power BI. Then put a Slicer visualisation on thePower BI report to filter the data down to Today. This video may help with that :https://youtu.be/VaJ10ilXp6Y

Or

Again, remove the Today() filter from your Excel file then use Power Query to filter out just Today from the data in the data load step. This video may help if that's your preferred option. https://youtu.be/hidJ5T_DYQ0

Hope this helps

Stuart

[anna_c]

Thank you! I have gone with the second option and managed to get it working