Auto Refresh Excel File Connected to Semantic Model with Analyze in Excel

[Dan44]

Hi all,

I have a semantic model which I have used Analyze in Excel to create an Excel Report. So the connection type is the following:

While the default is the file gets automatically saved to your OneDrive, I have it saved on SharePoint instead.

The issue is unlike a PBI report, when the semantic model is updated in Fabric, the only way currently the Excel Report refreshes is by opening the file from within the desktop app. That is, even opening the file with Excel Online within SharePoint, the file will only synch with the semantic model refresh if the following is enabled:

This is very limiting as I want this Excel file to be auto refreshed for the people I have shared it with and without complication (i.e. having to click Enable Content each time, and they may just have view only access). Furthermore, it also means I can't just have the files published to a PBI workspace because when files are opened there, it isn't by default opened in Excel Online / Desktop App.

Therefore, is there any good way to have an Excel file in SharePoint that's connected to semantic model be automatically refreshed, without the above limitations? 

Thanks

[JGroothedde · Accepted solution]

Hi @Dan44 ,

You can refresh the Excel files through Power Automate, see this helpful post for more information.

I have just tested this myself an can confirm it still works. The script in question is:

function main(workbook: ExcelScript.Workbook) {
	// Refresh all data connections
	workbook.refreshAllDataConnections();
}

After creating the script, create a PA flow to run a script and you're good to go:

Cheers!

[Dan44]

@JGroothedde this works great, thanks!

[JGroothedde]

Great! Glad i could help!

[Dan44]

@JGroothedde while this works great for Excel files using Analyze in Excel, my scheduled refreshes aren't working for files using Power Query with a Power Pivot data model configured. I wonder if you have encountered this issue before? Note I created a new post: https://community.fabric.microsoft.com/t5/Service/Auto-Refresh-Excel-Report-with-Power-Pivot-Data-Mo...

Would really appreciate the help

[foodd]

Hello @Dan44 , and thank you for sharing your concern. Consider raising this as a new Idea. This form would allow community members to contribute feedback and their level of interest through a vote. Thank you!

Issue

If you have found a true bug or issue, you can make a post to the forum to let others know (and that would be very nice of you to do so). But if you want to get it fixed, you should post it in the Issues/Ideas (it varies) area of the community. Specifically, you should post it here:

Power BI - https://community.powerbi.com/t5/Issues/idb-p/Issues
PowerAutomate - https://powerusers.microsoft.com/t5/I-Found-A-Bug/bd-p/BugFound
PowerApps - https://ideas.powerapps.com


Ideas

If you have a new idea for functionality or how something should work, you can certainly start a thread on the community to solicit feedback on your idea, but you should also post the idea in the ideas section of the website and solicit others to vote on your idea. The development teams use the Ideas area of the communities to build their development pipeline and a significant amount of weight is placed on Ideas that have lots of votes versus those that do not. The ideas areas of the communities are here:

Power BI - https://ideas.powerbi.com
PowerAutomate - https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas
PowerApps - https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[Dan44]

@foodd thanks and yes it would be something worth raising as an idea for - seems like quite a basic task that doesn't yet have integrated functionality 

[foodd]

@Dan44 , @JGroothedde 's suggestion is quite good.  Practical in nature and my go-to. Your thoughts address a larger issue that would improve the overall consumer experience.

Ideas

If you have a new idea for functionality or how something should work, you can certainly start a thread on the community to solicit feedback on your idea, but you should also post the idea in the ideas section of the website and solicit others to vote on your idea. The development teams use the Ideas area of the communities to build their development pipeline and a significant amount of weight is placed on Ideas that have lots of votes versus those that do not. The ideas areas of the communities are here:

Power BI - https://ideas.powerbi.com
PowerAutomate - https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas
PowerApps - https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas