64-bit Excel Power Pivot stuck on "reading data" for a small one-to-many relationship

[AmirT2150]

Hi,

Can anyone please tell me why the Excel Power Pivot for this simple one-to-many data model never finishes "Reading data..."?

Masters has 6000 rows

Sub Accounts has 63,000 rows

The same model works as expected in Power BI desktop!

[#2]

@AmirT2150 

That could be an issue with .net framework loading, add-ins could affect, could happen if schema was changed (e.g. field in model was renamed), something with connection or data source parameters. Whatever.

I'd try

- on another PC if available

- in excel /safe mode

- check if data source settings are exactly the same in Excel and Power BI Desktop

- build model from scratch in new Excel

If nothing helps when it's better to discuss with sample file.

[#3]

@SergeiBaklan thanks for your reply.

I found the solution: I needed to put the account number in the Values part of the PivotTable Fields pane of Excel (and change to show "Count of AccountNumber")!!! This way, the PivotTable is nice and quick.

As soon as I remove the "measure" from the Values section, the issues comes back when I mix fields from the Masters and SubAccounts tables. There is no performance issue if I only pivot on fields from the same table.

This is clearly a bug in Excel because I cannot recreate the issue in Power BI Desktop.

[#4 · Accepted solution]

@AmirT2150 

I see, thank you for the feedback. PivotTable, if no aggregation (other words nothing in Values) returns Full Join of two tables which are in relationship. In your case that's 63,000 x 6,000 = 378 million of values.

That takes time, especially taking into account that, most probably, for major part of the data is used memory cache on hard drive. 

[#5]

Hi @SergeiBaklan thanks for your explanation which has now made me understand the underlying issue.

I guess the Power BI Desktop team have put in additional code to avoid this FULL join.