Excel 365 - PowerQuery Performance

[ygaft]

Hi Everyone,

I am no newbie for PQ-PP world, made some great stuff with it, but now-days have some project that just kills me.

I have a set of queries that works ok-ish (not top notch speed but ok) on small datasets like below 11K rows. Over that Power-Query eats all available RAM (about 10-12 GIGS) and in the end fails to refresh queries. Same queries work just fine in PowerBI desktop (with way larger dataset) which is weird as hell.

I am trying to find tool/solution to find out how to improve performance / check where the bottleneck is, every help will be appreciated!

Thanks in advance

[#2]

@ygaft 

Power BI Desktop has built-in diagnostic tool, to work with DAX queries you may use DAX Studio. However, I'd learn before other people experience. You may google for Chris Webb series of related to Power Query performance blogs and check sqlbi.com for the topics related to data model.

I don't think there is the tool which says you directly where exactly is the bottleneck and what to do with that. Usually that's iterative process and requires some understanding of how things actually work. Thus following to recommendations like "this way is preferable and avoid such things" could be enough.

[#3]

@Sergei Baklan 
First of all thank you for your time.
"Power BI Desktop has built-in diagnostic tool, to work with DAX queries you may use DAX Studio."
- I know that, tried to merge current queries to PBI and run diagnostic tool - nothing useful there. About Dax studio I am not sure since in my opinion the problem is at Query level and not DAX (PP) level. I've used PP Utils tool to try find memory consuming columns, also no luck that path...

"However, I'd learn before other people experience. You may google for Chris Webb series of related to Power Query performance blogs and check sqlbi.com for the topics related to data model." - I've read all the data available out there, tried all the tricks but my feeling is that I am out of cards in a sleeve (tried multiple methods). I think that I will go down the dark path of killing measures/calc columns since if I load the query straight forward without connections and calculations it loads pretty fine...

" Usually that's iterative process and requires some understanding of how things actually work" - Indeed, it's not my first ride down that road (with these tools about 5-6 years) - I am just stuck and trying to find some info.

Once again thank you for your time!