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

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

Reply
jeppesal
Advocate II
Advocate II

Error with Analyze in Excel report in Power BI

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 Smiley Happy).

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?

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

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

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

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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors