Excel on SharePoint not updating, therefore refresh is not updating visualization

[samanthamirae]

Hello,

One of my Gantt charts in PBI is connected to an excel sheet on SharePoint. There is a =TODAY() column in the Excel sheet and that is very important to keep my Gantt chart up to date. When there are less than 90 days to complete the task, the line in the Gantt chart turns from green to red, but this does not happen because the TODAY() column in excel doesn't update unless I click into the Excel sheet from SharePoint folder and re-enter the TODAY() cell and save, which then updates the Excel sheet. 

This defeats the entire purpose of my PBI dashboard if the Gantt chart timelines cannot automatically turn from green to red and visually notify us. I would prefer not having to go in the Excel every day to ensure that the TODAY() column is updated. 

Any suggestions? Maybe a way to refresh the Excel sheet itself? I guess this might be more of a SharePoint Online Excel issue than Power BI, but I was wondering if anyone else experienced the same thing. 

Thank you!

[samanthamirae · Accepted solution]

I was able to figure this out myself by creating a Microsoft Flow to "add a row in excel table" but it would just add an invisible space (" ") to all the columns so it doesn't alter the data but just refreshes the Today() column 

[v-yulgu-msft]

Hi @samanthamirae,

Once the dataset is updated in Excel sheet hosted on SharePoint online, report view on Power BI side will be updated automatically per hour or scheduled refresh depend on the way connecting to Excel. Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

However, in your scenario, the implement to make the TODAY() column to be updated daily is a requirement more related to SharePoint online. Maybe you can have a reference to this thread: How to auto refresh Excel sheet in SharePoint document library. For more professional advice, I would suggest you post question on SharePoint forum.

Regards,

Yuliana Gu