Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dan44
Helper I
Helper I

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

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:

Dan44_0-1718803080967.png

 

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:

Dan44_1-1718803080970.png

 

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

1 ACCEPTED SOLUTION
JGroothedde
Resolver I
Resolver I

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();
}

 



JGroothedde_0-1718804514762.png

 

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

JGroothedde_0-1718805312702.png

 

 



Cheers!

View solution in original post

7 REPLIES 7
JGroothedde
Resolver I
Resolver I

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();
}

 



JGroothedde_0-1718804514762.png

 

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

JGroothedde_0-1718805312702.png

 

 



Cheers!

@JGroothedde this works great, thanks!

Great! Glad i could help!

@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
Super User
Super User

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.

@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 

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.