Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

6 REPLIES 6
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

 

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.