cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA
Solution Sage
Solution Sage

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.

1 ACCEPTED SOLUTION

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

 

View solution in original post

5 REPLIES 5
aj1973
Community Champion
Community Champion

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 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

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

 

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?

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

 

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors