Forum Discussion
Helper IExcel connection to Power BI datasets, "Insert Pivot Table" button, does not keep the relationships
Within Excel, I am connecting to the Power BI datasets through the Get Data -> From Power Platform -> From Power BI
When doing so, there are two options to connect: "Insert PivotTable" and "Insert Table":
When "Insert PivotTable" is selected, the relationships in the dataset are not recognized, making this function useless. The relationships are maintained when using the "Insert Table" option and provide correct data.
Does anyone else face this issue? What can be done to get this to work as intended?
4 Replies
- lbendlin
Super User
Did you mean to say this the other way round? PivotTable = cube connection. Table = DAX query.
TakeoffGoatMigoNope, I'm seeing the relationships are struggling to hold when using the "Insert PivotTable" connection with Power BI datasets.
The relationship between these tables seems to only be crashing when stacking fields in the "Rows" option and creating a hierarchy. Here is what the pivot table shows when one field (Field A) is in the rows and the relating field (Field B) is in the values:This is the correct value, as each should have a count of 1.
Here is are those same fields when both are in the rows field, creating a hierarchy:The relationship no longer exists between these tables and all the values for Field B are showing, proving the lack of relationship. When this same function is performed inside the Power BI application, this does not occur. I have tested this with a variety of machines, datsets, tables, and fields and found that this error is consistent.
Have you seen anything like this before?- lbendlin
no, not familiar with that scenario or error pattern. I hope someone else can help you further.
