Forum Discussion
Excel Not Refreshing Data from Power BI Semantic Model (Live Connection via XLMA)
Hi everyone,
I'm trying to create a fully automated reporting process by connecting an Excel workbook directly to a Power BI semantic model using the XLMA endpoint (live connection).
The goal is to have Excel always reflect the latest data from Power BI, without needing to manually export reports or paste tables. I’ve written a script that opens and refreshes the Excel workbook each morning to ensure everything stays up to date.
The problem: Even after running the refresh (via script or manually in Excel), the data doesn't actually update. It continues to show outdated values, even though Excel successfully connects to the latest version of the semantic model in Power BI.
I've already confirmed with my Power BI team that the semantic model is working correctly and refreshing as expected. Their suggestion is that it's an Excel-side issue.
Details:
I'm using Excel for desktop (latest version) with a Power BI Pro license.
The data is pulled via "Get Data, From Analysis Services" using the workspace connection URL.
There are no errors during refresh, but the values don't update. Later on, I sometimes do get error messages but not always and never during the initial refresh. The message is along the lines of: "We couldn’t get data from the external data source. The (blank) session ID cannot be found. Either the session does not exist or has already expired."
If I manually rebuild the pivot table, it does pull the correct current data, but that defeats the purpose of automation.
Has anyone encountered this issue? Is there a known limitation or workaround for ensuring Excel truly refreshes the live connection to the semantic model?
Appreciate any insight or suggestions! Has anyone encountered this issue? Is there a known limitation or workaround for ensuring Excel truly refreshes the live connection to the semantic model? Appreciate any insight or suggestions!
2 Replies
IMHO, XMLA endpoint works with Premium capacity. Plus there are some limitations on semantic model. With Pro license works connection established as Data-> Get Data-> From Fabric & Power Platform -> From Power BI -> select semantic model -> Insert PivotTable (or Table).
Please try to fix by below:
- Force Reconnection: Instead of just refreshing, try scripting a full disconnect and reconnect to the semantic model before triggering the refresh.
- Disable Pivot Table Caching: In the PivotTable Options, uncheck “Save source data with file” and “Enable background refresh.”
- Use Analyze in Excel: If possible, use the “Analyze in Excel” feature from Power BI directly, which sets up a more robust connection.
- Check XMLA Endpoint Settings:
- Ensure the XMLA endpoint is enabled for your workspace.
- Confirm that the tenant-level export data setting is turned on in the Power BI Admin Portal.
- Session Management: If you're using service principals or automation, make sure the session is correctly authenticated and hasn’t expired mid-process.