Forum Discussion
Solution SageMore efficient running total column in Power Query?
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
Community ChampionHi 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
Solution Sageaj1973 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
Community Support
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
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
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