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

Working with connection based Excel dashboards in PBI Service

Hi, I have an Excel file with pivot charts that gets the data from a connection to SSAS and has not the datamodel inside of the Excel file. I.e. data is not imported into the Excel file (using Power Query), it is based on a connection (using an .ods file). Is there a way to publish this Excel dashboard to Power BI Service and setup this connection between SSAS and the Excel dashboard in Power BI Service through Power BI Gateway? The scenario is basically the same as with a PBIX file that uses Live Connection, but unlike with the PBIX file, Power BI service does not ask me to set up the gateway to establish a connection for the Excel file. We have plenty of such Excel dashboards and it would save us a lot of time not to rebuild them all in Power BI Desktop in order to have functional dashboards in Power BI service from up-to-dateness perspective. Unfortunatelly, the import of these Excel files into Power BI Desktop for a simple conversion does also not work with such connection based Excel files.
I have gateway set up for published PBIX files using live connection and from gateway perspective everything works fine. I do only struggle with making use of this with my Excel dashboards in Power BI service.
Power BI-Excel-SSAS Connection.png

I also found this post from 2018 https://community.powerbi.com/t5/Service/Live-Connected-Excel-Workbook-in-Power-BI-Service-is-not/m-... but it doesn't really come to my point because I never refresh these kind of Excel files. There is no such thing like "refresh all" with Power Query queries in my files because there are no queries in these files, only connections. When I open them, they connect to SSAS and get the latest data. I need the same behavior in PBI service as well, and it works for published PBIX files with Live Connection. But not with my Excel files.

2 REPLIES 2
Community Support
Community Support

Re: Working with connection based Excel dashboards in PBI Service

hi @Martin_Nbg 

To my knowledge, it may couldn't achieve in power bi for now.

as the old post said, if the excel file in import from onedirve or sharepoint, power bi service excel will keep same with the it in onedrive or sharepoint.

 

Regards,

Lin

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

Re: Working with connection based Excel dashboards in PBI Service

Hi Lin,
Seems like you are right: There is no way to reuse *.xlsx files that use SSAS connection as a datasource and keep them linked to latest data from SSIS after publishing to Power BI. This works only with *.pbix files.

I've put the file on OneDrive and tried publishing from Excel and pulling the file into Power BI from the web-browser, and it didn't help anything: Data refresh not supported 😞 

BR

Martin

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.