cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chintanpatel_za Frequent Visitor
Frequent Visitor

Excel workbook stored in OneDrive for Business is not refreshing certain functions

Hi,

 

I have an Excel workbook stored in OneDrive for Business and it's being used as a dataset for a report I have in the Power BI service.

 

This particular Excel workbook has one of the date functions in several cells: =today(). With the nature of this function, everyday, the workbook with those cells must update with the most recent date.

 

I have set up a scheduled refresh with the report in Power BI with this Excel workbook and so technically, if the most recent date reflects in the Excel workbook, then the most recent date should also then pull automatically into the table visualisation I have in a report in the Power BI service.

 

However, this is not the case. I find that the report is not refreshing automatically despite schedule refresh settings switched on and correctly configured. I am storing the Excel workbook in the cloud, so I don't need to use a personal gateway. I have to manually open the workbook from OneDrive, and then the date changes. Then it saves back to OneDrive, and then I have to do a manual refresh and then the reports get updated with the correct dates.

 

Is this a flaw in Power BI? Or something unsupported? Something I should suggest as a new feature? Or am I doing something wrong? Please advise?

3 REPLIES 3
v-cherch-msft Super Contributor
Super Contributor

Re: Excel workbook stored in OneDrive for Business is not refreshing certain functions

Hi @chintanpatel_za 

Please have a look at the document:Refresh a dataset stored on OneDrive or SharePoint Online

'When you store a Power BI Desktop file on OneDrive or SharePoint Online, any data you’ve loaded into your file’s model is imported into the dataset, and any reports you’ve created in the file are loaded into Reports in the Power BI serviced. When you make changes to your file on OneDrive or SharePoint Online, such as adding new measures, changing column names, or editing visualizations, once you save the file those changes will be updated in the Power BI service too, usually within about an hour.'

Some threads for your reference:

https://community.powerbi.com/t5/Integrations-with-Files-and/Automatic-Refresh-not-working-when-conn...

https://community.powerbi.com/t5/Service/Auomatic-Refresh-reports-successful-refresh-but-doesn-t-rea...

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chintanpatel_za Frequent Visitor
Frequent Visitor

Re: Excel workbook stored in OneDrive for Business is not refreshing certain functions

Hello. It's not working. Here's the schedule refresh history:

 

Refresh 1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

Refresh 2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

And here's a screenshot of the data as of 23 June 2019 16:22 (UTC+2):

 

MOS.PNG

 

 

 

 

 

 

 

 

 

 

 

 

You can see the data on the bottom left. All those dates in the "Valid To" column that reflect "16 June" are supposed to show the current date (23 June) as per the Excel formula =today() . Based on your answers above, I followed the necessary steps, and ensured the workbook/dataset is stored in the correct location and with the schedule refresh settings configured correctly, but the refresh does not work with formulas like =today().
 
Please advise?

chintanpatel_za Frequent Visitor
Frequent Visitor

Re: Excel workbook stored in OneDrive for Business is not refreshing certain functions

Hello.

 

I haven't received any response to this matter.

 

Here's the updated refresh logs. the scheduled refresh is happenening normally:PowerBI_Refresh.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

But my data still hasn't updated:

MOS.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please advise if this is something I have to report in the PowerBI uservoice? Or is there a fix to this?

 

The date 16 June must reflect today's date or the current date.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 147 members 1,846 guests
Please welcome our newest community members: