Changing Pivot Table Fields on Dataset Fails with Excel Workbook in Workspace

[Martin_D]

Hi,
I have two Excel workbooks in my workspace.

Eachcontains a Pivot table.

In the first one, the data for the Pivot table comes from a Power BI semantic model.

In the second one, the data comes from the Power Pivot model inside of the Excel file.

Now, when I open the file with the Power Pivot inside of the file in Power BI, then I can modify the fields in the Pivot table, which is a nice and easy way to allow users to slice and dice by whatever they want.

If I open the file with the data from the Power BI data model then I still see the UI to modify the fields of the Pivot table, but when I modify the fields I get an error and cannot load the data:

But when I open the same Excel file in the Excel web app, then it works, so it's obviously not a problem with my privileges on the semantic model or a general limitation of the Excel web app.

How can I solve this error in the Power BI workspace view of the Excel file? Is this behaviour by design, as intended by Microsoft, or is this a bug?

Kind regards,
Martin

[v-nmadadi-msft]

Hi @Martin_D ,

I wanted to check if you had the opportunity to review the information provided. Is there any update regarding your issue? Please feel free to contact us if you have any further questions. If you have tried any other troubleshooting step and it worked, please let us know and accept it as solution so as to help other community members

Thank you.

[Martin_D]

Hi @v-nmadadi-msft ,

Thank you for following up on  this issue. Unfortunatelly your referenced article does not relate to the problem. The article describes how to connect from Excel to a Power BI semantic model, not about embedding such Excel file into a Power BI workspace. As I already wrote in the initial post, in the Excel Web app and also in the Excel Desktop app everything works fine.

Kind regards,

Martin

[v-nmadadi-msft]

Hi @Martin_D ,
Sorry to know it didn’t help. Please consider reaching out to Microsoft Support. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious. 

Below is the link to create Microsoft Support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks and Regards

[v-nmadadi-msft]

Hi @Martin_D ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Martin_D]

Hi @v-nmadadi-msft ,

No, it's not solved. May I kindly ask you to recreate the problem on your site, i.e., create in Excel a Pivot table from any Power BI semantic model in your tenant, save the Excel file to SharePoint Online, add the Excel file to a Power BI workspace (no "my workspace"), open the Excel file from the workspace in Power BI, modify the Pivot table fields and check whether the data in the Pivot table updates on screen or whether you see the same error? Then at least you can tell whether it's only me having the problem and what you did to not have the problem.
Thank you very much!
Kind regards,

Martin

[v-nmadadi-msft]

Hi @Martin_D ,
I did not face the same issue when i tried to replicate it in my end.
Please check out this limitations document and check for any possible fixes:Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn.
If that does not help that please consider reaching out to Microsoft Support. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious. 

Below is the link to create Microsoft Support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks and Regards

[rohit1991]

This behavior is by design. In Power BI, Excel relies on centralized data connections and refresh policies. The "Data Refresh Failed" error indicates issues with the "PracticeDatasetSharePoint" connection or gateway configuration.

Solution:

1. Verify the "PracticeDatasetSharePoint" connection in Power BI.
2. Ensure the required gateway is configured and running.
3. Test and resolve dataset refresh issues directly in Power BI.

If the file works in Excel web app, the issue is specific to Power BI's stricter refresh framework.

[Martin_D]

Hi @rohit1991 ,
thank you for analyzing the issue. What would be a correct summary of your comment: There is a way to fix this issue, or there is no way to fix this issue?

Actually, there is no gateway involved, so I can exclude this as the cause. Between the Excel file and the Power BI semantic model there is for sure no gateway and between SharePoint Online as the data  source of the semantic model and the semantic model there is also no gateway. I'd be surprised anyway how this would affect the scenario.
Kind regards,

Martin