OneDrive Excel Dataset does not Refresh Tables with Formulas

[Anonymous]

I have Excel files with tables in OneDrive for Business that have formulas including the Today() function. As a due date approaches, the formulas count down days until due. However, the datasets in Power BI do not refresh unless I manually open the file in OneDrive, change data, and save. I would like the data on my Power BI dashboard to refresh in the background everyday as the day (Today()) changes. Is there a solution that doesn't involve me opening and closing these Excel files everyday?

[V-lianl-msft]

Hi @Anonymous ,

You can refer to this :https://community.spiceworks.com/topic/2145151-excel-not-updating-formulas 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft 

I checked and all formulas are already set to "automatic."

I wonder if the issue is that Excel does not update those formulas automatically on in OneDrive unless the file is opened, or Power BI is not checking for new data if a new Excel file version isn't saved. Does this make sense?

Thank you.

[V-lianl-msft]

Hi @Anonymous ,

This means that if you connect to excel on onedrive, when excel is updated, power Bi will also synchronize after refreshing the dataset.When the excel file is not updated, the dataset refresh of power Bi will not be updated.

If automatic calculation has been set in Excel, but it can not solve the issue, it is recommended that you use Excel on onedrive only as the data source and put all the calculation in power Bi.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft ,

I understand in prinicple but how do I set up Excel in OneDrive only as the data source? I've tried  "Connect, manage, and view Excel in Power BI" but it tells me that data refresh is not supported because the document does not have a Data Model, even though it does.

Thank you.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

How to connect files on onedrive can you refer to this:https://www.sumproduct.com/blog/article/power-bi-tips/power-bi-using-onedrive-and-setting-up-automatic-updates?L=0 

https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links 

You can refer to this for onedrive refresh :https://docs.microsoft.com/en-us/power-bi/refresh-data#onedrive-refresh

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.