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 ,

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

[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