Error with Analyze in Excel report in Power BI

[jeppesal]

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:

1. Importing the local Excel file to Power BI using "Uploading your Excel file to Power BI". When using/updating the pivot table I first get a Data Connection security warning. When selecting "yes" i get an error "External data refresh error".
2. Linking to the Excel file on OneDrive using "Connect, manage and view Excel in Power BI". I get the same warning followed by the same error. This used to work back in June (pretty sure ).

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?

[Anonymous]

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

[jeppesal]

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

[Anonymous]

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

[jeppesal]

Hi Lydia,

I have reposted as an idea as you suggested.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15555081-support-for-refresh-pivo...

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