How to optimize Power BI queries for best performance and easier maintenance. How to use the new query diagnosis to measure performance and understand the timing for each query step.
Great to see this. I'm a bit of an obsessive over tidying up my transforms into as few steps as possible. I didn't realise it had such a bearing on performance so I'm glad I have an additional, practical reason to so heavily optimise my transforms now!
I noticed this too. Thank you. If you could expand on this topic, that will be great. For example, could you go back to a step and add more actions to itself. Is there a tool that would "look into your" power Query and "Optimize" it for you?. You could explore what actions are more time-consuming than others. For example, merging, vs filtering, vs replacing values.
The biggest issue i have in PowerQuery regarding flagged memory errors are when i need to use 'group by' for mass amounts of data and then un-'group by' to get the underlying line details back. Still have not been able to solve for this other than maybe order some more RAM memory to hopefully address this.
hi and thanks. i am wondering what to do about "Add Index" step that i use 2 times after one another, it takes too much time according to query diagnostics?
Great! I have one question Paul : in therms of memory addressed , are all those steps treated by PowerBI as a sort of new variable each that never gets erased till the end of the steps? just asking would you suggest to lower the ram used by forcing reusing the same 2 or 3 'names' of variables/stepnames to load the dataset output of each step in fewer ram (just in case of many programs opened and less write to disk operation which may improve overall speed of execution?). Have you ever tested or had experience of improved performance as such? thank you
