Excel Web API refreshing successfully, but data not updating

[kakenned]

I've searched for an answer to this but haven't found anything, so sorry if this has been answered somewhere already.

I have two different reports that I'm having this issue with. I have excel files housed on Sharepoint and am using a Web API connectors for them as a dataflow. These are set up for schedule refresh, and according to the service the refreshes are successful (running every two to four hours day & night). The problem I'm having is the reports have a page that is supposed to show last week's performance. This is not updating when a new week starts (our week starts on Monday and the service, to my understanding, starts on Sunday, but by Monday morning the data is stuck a week behind what it should be). On Monday morning if I go into the excel files and manually refresh all data sources and then refresh the dataflows and semantic models, the report will now show the correct data for the previous week. 

Things I have tried to fix this: 

I have blank queries in the excel files that just get the DateTime.LocalNow() that are set to refresh every 60 minutes and refresh on open, but this is obviously not telling the dataflow we're in a new week. One thing to note is that enable background refresh is not selectable on this. Not sure if that is because the files are housed on sharepoint or if it's a security setting controlled by my IT group.

I've tried creating flows in Power Automate that (are supposed) to open the excel files and refresh all data sources every 30 minutes from midnight sunday until monday morning. The flows are completing successfully, but the dataflow is still not updating for the new week.

I've tried flows to get file content and update file content of the excel files (both the sharepoint and one drive versions). Again the flows are completing successfully, but the dataflow is still not updating for the new week

I am at a loss and don't know if it's an excel issue (I'm assuming it is at this point since all scheduled refreshes on the dataflow side & all the flows on the power automate side are completing successfully). Does anyone know of a fix for this? 

[v-pnaroju-msft]

Hi kakenned,

We are following up once again regarding your query. Kindly confirm whether the issue has been resolved through the support ticket with Microsoft. If the issue has been resolved, we would be grateful if you could share the resolution or key insights here to assist others in the community.
In the event that we do not receive a response, we will proceed to close this thread. Should you require any further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum by initiating a new thread. We will be happy to assist you.

Thank you.

[v-pnaroju-msft]

Hi kakenned,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.
Just following up to confirm if you’ve raised the support ticket. If so, please share any feedback or solutions received. Kindly consider marking the solution as accepted to assist the community.

Thank you.

[v-pnaroju-msft]

Hi kakenned,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.
Just following up to confirm if you’ve raised the support ticket. If so, please share any feedback or solutions received. Kindly consider marking the solution as accepted to assist the community.

Thank you.

[v-pnaroju-msft]

Hi kakenned,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.
Just following up to confirm if you have raised the support ticket. If so, please share any feedback or solutions received. Kindly consider marking the solution as accepted to assist the community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin , for your response.

Hi @kakenned,

We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.

In addition to the response provided by @lbendlin , please follow the steps mentioned below, which may help resolve the issue:

1. Open your Excel file, navigate to Formulas > Calculation Options, set it to Automatic, and ensure that Power Query steps (such as DateTime.LocalNow()) refresh properly when using Refresh All. Save the file and re-upload it to SharePoint.

2. Create a flow to run on Sunday at 11:30 PM EST. Use Excel Online (Business) > Run Script with a script that forces recalculation. Then, add Power BI > Refresh a Dataflow to update the data immediately after execution.

3. In Power BI Service, ensure that the dataflow refresh schedule is aligned with the flow and is not caching outdated data.

4. Execute the flow and dataflow on Sunday night, and verify the report on Monday morning to confirm that it displays the correct data for last week.

Additionally, please refer to the links below for further assistance:
Power Query SharePoint folder connector - Power Query | Microsoft Learn
Troubleshoot refresh scenarios - Power BI | Microsoft Learn

If the solution did not resolve the issue, we kindly request you to raise a support ticket using the link:Microsoft Fabric Support and Status | Microsoft Fabric

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.

[lbendlin]

I have excel files housed on Sharepoint and am using a Web API connectors for them as a dataflow

Consider using the SharePoint Folder connector instead.

our week starts on Monday and the service, to my understanding, starts on Sunday, but by Monday morning the data is stuck a week behind what it should be

What is the timezone behind "our"  ?  The Power BI service runs on UTC.

[kakenned]

Ok I will try that connector out. 

We are EST so either 4 or 5 hours behind UTC depending on the time of the year. The time difference shouldn't matter in this case, as it's excel that isn't updating and excel uses the system date. So whenever it hits midnight and that new week is in effect it shouldn't care that the service is on UTC because it's also a new week on that time also. Am I thinking about this correctly?

[lbendlin]

I think so.  If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .