Forum Discussion

AmirT2150's avatar
AmirT2150
Copper Contributor
Jul 25, 2024

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

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!

 

 

 

  • 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. 

  • 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.

    • AmirT2150's avatar
      AmirT2150
      Copper Contributor

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        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. 

Resources