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

     

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

 

RMDNA's avatar
RMDNA
Solution Sage
5 years ago

Hi v-easonf-msft, thanks for the response.
Can you think of a more efficient way to perform the same operation in PowerQuery that doesn't involve needing that duplication of effort?

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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

     

    • PowerBIWannabe's avatar
      PowerBIWannabe
      Regular Visitor
      2 years ago

      So you're saying is best to use the DAX calculated column, but I do run into an issue doing so. My problem is that I'm using a high amount of rows (~100,000) that I need to perform a running total and I refresh the report I get a memory error, "There's not enough memory to complete this operation. Please try again later when there may be more memory available."

      The PBI file size is 17MB and I know that is not the problem, what the problem is I know for sure in the running total calculation because when I take those out, the report refreshes with no problem.

       

      Do you have a suggestion to have running totals with no problem despite the amount of the data?

       

      Your help is much appreciated.