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
MViolette
Frequent Visitor

Slow performance within Power BI Desktop, Query editor, Applied Steps.

Hi all,

 

I tried connect to the Dynamics CRM Content pack online without sucess. So now i'm trying to connect to Dynamics through either Power Query or directly with Power BI desktop.

 

I am facing issues in the Query editor, After doing so many applied steps, each new step seems to take longer and longer to do. to it gets to a point where i'm waiting ages to do something fairly simple, such as replace values.

 

I've unchecked the relationships options, thinking this may help.

 

Perhaps there is too much data shaping involved in my process?

 

Any help would be appreciated.

 

Regards,

Mike.

16 REPLIES 16
Neil_Message
Regular Visitor

I have just connected Dyanmics 365 CRM to Power BI and I am experiencing issues with being able to edit in Power Query Editor. The slowness hurts.

Is there another thread anywhere that explains how to speed this up?

ianbarker
Helper III
Helper III

Does anyone have a solution to this yet?

Using Dynamics CRM Online is so painfully slow in Desktop that it's pretty much unusuable.

I've started to try and use a tool called XRM Tool Box, this has a plugin called Power BI Option-Set Assistant. This will map all your option sets for you, massively reducing the number of steps that needs to be taken in Power BI when editing your query. Give it a go. Here's a link to how it works: http://www.gapconsulting.co.uk/news-resources/blogs/entryid/141/xrmtoolbox-the-powerbi-option-set-as...

cwilliams
Helper II
Helper II

I am having the same issue.  I am making simple updates in Query Editor, formatting data in 1 specific column and it could take hours for it to me done.

 

It seems that the speed has declined with each new version of PowerBI. 

 

I think you have a good point about the number of steps within a query.

 

I definitely need a resolution soon. 

 

Cheryl, South Florida

Not a solution, but some discussion. Power Query is very aggressive about reloading the query preview. It seems that each additional step can result in re-reading the source and re-applying intermediate steps. If you have expensive query steps or a large data source, I've seen this slowdown. With well-optimized transformations and sufficient disk throughput, I don't see this sort of slowdown.

I've experienced 2 types of slowness:

1) When editing the query

2) When executing

 

When doing multiple steps the editing can be slower than the execution itselft. So what I'm doing in these cases is to insert a temporary date-filter as one of the 1st steps which I later remove. This keeps the editing process quick.

 

For certain operations it can also help to use Table.Buffer or List.Buffer: In order to prevent that the same query will be executed multiple times. But as this will prevent query folding, it can means trial and error to see whether it is actually benefitial.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I don't know if this new option helps with issues like this, but worth testing out against this use case... In the latest update there is a new option you can disable so a preview of data isn't generated on the backend every time you make a change.

Update blog - Look for the Option to disable data previews in the background or it is further explained in the video at 16:40

 

Can be found in the Desktop under File - Options and Settings - Options - (Current File) - Data Load - Background Data

 

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

I took the above suggestions about resolving the performance of query editor.  It does not seem to make a difference to filter on a specific date when the query has more than 20 applied steps.  Is there a maximum number of applied steps allowed for a query?

 

There were two steps that I used to speed up the query editor process:

 

1)  Unchecked the "Allow data preview to download in the background."  Make sure that this is unchecked in the report view and query editor.

 

2)  The other option that I unchecked was "Enable refresh of this query."  This is located in the Query Editor under Properties.  DO NOT UNCHECK "Enable load to report."....you will lose the report.

 

Let me know if this assists anyone or if anyone has other solutions.

 

Cheryl W.

South Florida

Anonymous
Not applicable

Disabling the "Allow data preview to download in the background" helped me. Thanks!

I made both those suggestions and the problem is resolved. Thanks!

Just an update, none of the above suggestions have improved the speed of the applied steps in my query. Any one else able to help?

Anonymous
Not applicable

Is there a feature we can vote to fix this ?

I am currently working on a complex data model with lots of queries.

Power BI is slowing down like crazy, every time I have to add a new step or a new query.

 

I can't make my deadline because of this Smiley Sad

APACK01
Frequent Visitor

I'm having the same issue, even after just 6 or 7 applied steps.  I would love to hear if anyone has had a similar issue or any tips.

I've still no further insight into this. Please let me know if you find a way around the problem.

I have exactly the same problem - with very little data it's taking about 10 minutes for each step - even renaming a column is taking ages. Does anyone have any idea how to speed this up?

I run into the same problem these days.

Update strategy to reduce refresh times in PBI Des... - Microsoft Power BI Community

 

Since this post is 5 years old. What has happened over last years?

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.

Top Solution Authors
Top Kudoed Authors