The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I have a Power BI Services dataset (Power BI model created with Power BI desktop) and created a Excel pivot table using "Analyze in Excel". I would like to view and work with this Pivot table in Power BI Services.
I have tried:
I have tried both "Use the authenticated users account" and "None" options in the Excel Services Authentication Settings on the Data connection in the Excel file.
Does anyone know what Microsoft changed and/or how to make this work?
EDIT: I guess it has something to do with the new Integrated Security=ClaimsToken not supported in Excel Online?
Hi @jeppesal,
Based on your description, in Power BI Service, you use the Analyze in Excel option to generate a .ODC file of your dataset, then create PivotTable in the file, right?
If that is the case, as stated in this article, we can save this Power BI dataset connected workbook , but we cannot publish or import this workbook back into Power BI. Since the new workbook simply has a connection to the dataset in Power BI, publishing or importing it into Power BI would be going in circles!
Thanks,
Lydia Zhang
Hi Lydia,
Thank you for our answer. I have read that article and disagree about the circle way of looking at it.
If I used Excel as a data source it would be going in circles but I want to use it as a report tool. Like any Power BI report I would like my customers to use Excel Pivot tables in Excel online and/or view in Power BI.
This is a major feature that would extend the use and target group of Power BI and models created in Power BI.
Thanks,
Jeppe
Hi @jeppesal,
Without using "Analyze in Excel" to generate .odc file and creating PivotTables in it, just create another new clean Excel file and create PivotTables and charts, then Publish the Excel Workbook to Power BI following the instructions in this article, does it work as expected ?
In addition, regarding to the issue that re-import the Excel workbook which is generated by using "Analyze in Excel" to Power BI, I would recommend you submit a feature request in the idea forum: https://ideas.powerbi.com/forums/265200-power-bi-ideas.
Thanks,
Lydia Zhang
Hi Lydia,
I have reposted as an idea as you suggested.
I still can't see why it shouldn't work out of the box, but I guess it has something to do with the new authentication method.
Thanks,
Jeppe