none
Speed: FillDown on 1,244 lines of data then load takes 9 minutes RRS feed

  • Question

  • I have a power pivot query running on a fairly new laptop (although running 32 bit Windows 10 and 32 bit Excel 365).  The query takes in (from a table in the same workbook) 3,420 lines of data in 18 columns.  It then deletes most of the columns and filters the data leaving 1,244 lines of data in 6 columns.  A new calculated column is then added (a simple if... then ...else looking at the data in one column, and the .FillDown function then applied to that to fill in any nulls arising from that calculation.

    The table is then load to a worksheet.  All runs reasonably fast to the .FillDown but this on 1,244 lines of data and the subsequent load to the screen takes 9 minutes.   

    Is this the usual experience?

    If not, how should I trace down and resolve what causes this?

    Malcolm


    Friday, August 16, 2019 9:53 AM

All replies

  • Malcom,

    I do not use .filldown, but may now that I am aware of it. That being said, I process way more data in seconds, so I would say no this is not normal.  I assume you have a macro. you can try breaking the macro into steps and see if you can trap the issue.  I don't think .filldown will work with a pivot . Pivots are not updateable, so if you try to write over them, you should get an error.

    Adam


    Adam

    Wednesday, September 11, 2019 8:39 PM
  • By "macro" I presume you mean Power Pivot M formula code.   I have broken the code down.  It is the line Filldown line of code which takes the time.  Hence my question.  Why?

    Malcolm


    Friday, September 13, 2019 6:52 PM
  • Malcom,

    I googled "Power Pivot M formula" and it came back with "Power Query M Formula", if so this is a Power Query question vs power pivot.  I use Power Pivot but do not use Power Query.

    My guess is 3-5 seconds max for that many records. I load 1,000,000 records  in 10 seconds.

    Adam


    Adam

    Friday, September 13, 2019 7:57 PM