Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
5 years ago
Solved

More efficient running total column in Power Query?

Just a top-level edit for readers: I'm aware that Power Query isn't the place to do this, and I know how to write it as a measure just fine. I'm just curious 1) why it's as bad as it is, and 2) if I were to do it anyway, is there a better way to do it.

Hi all,

For a three column table (Date, Count, and Index), I have a running total column created in Power Query using the formula below:

 

= Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"Added Index"[Count],[Index])))

 

However, it has a massive performance impact, even on just 638 rows, taking several minutes to provide a preview. It's a major bottleneck in both PQ and report refresh times. 


Assuming I need it to be a column in Power Query (because I know the first suggestions will be "just make it a measure"), is there a more efficient/optimized way to do this?

Regardless of the answer, can anyone educate me on why this is such a resource-heavy query? I'm curious why it has such a disproportionate impact.

Thanks!

Edit: I just saw a sidebar note about the new Power Query forum! I'll make sure to use that in the future.

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi,  RMDNA 

    I think you may misunderstood what I meant.This is not a problem that can be solved by optimization.

    You can refer to this wonderful blog:

    comparing-dax-calculated-columns-with-power-query-computed-columns 

     

    The Power Query computed columns are not suitable for executing the aggregation on the data source, you should consider a DAX calculated column to avoid a long processing times.

     

    Best Regards,
    Community Support Team _ Eason

     

6 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Hi RMDNA 

    Power Query is used to clean and transform data and more less to execute calculations. So it is better to use DAX or add a SQL view 

    • RMDNA's avatar
      RMDNA
      Solution Sage
      5 years ago

      aj1973 Oh, I absolutely understand that PQ isn't the place to do this, and I know how to do it in DAX just fine, but my question still stands - if I were going to anyway, is there a better way to do it?
      It's partially just me being curious as to what it's doing in the backend that makes it this bad, just to educate myself.

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        Hi,  RMDNA 

        The query preview of the raw data consumes the cache, it doesn't really load the data, so it doesn't take up much memory. Compared to the directquery model , the import model has a data view. If the data to be loaded becomes larger, more memory and time is needed.

        The formula you provided seems to require traversing the entire table twice when performing the calculation (638*638). It is equivalent to increasing the data model that needs to be loaded.

         

        Best Regards,
        Community Support Team _ Eason