Forum Discussion
Power Query Editor REALLY SLOW - SQL SERVER CONNECTION
Hi Anonymous ,
Which connection mode you are using to connect SQL Server? Import or Direct Query? Is the underlying database running smoothly? It is difficult to give a suitable solution without sample data and related execution operations. Because there are many possible reasons(large amount of data, specific applied steps etc.)... You can try the following operations anyway and see if the situation has improved later:
1. Upgrade your Power BI Desktop to the latest version
2. Uncheck background data (File –> Options –>Data Load –>Allow data previews to download in the background)Any way to speed up really slow refresh times in Power Query?
3. Refer the following blog to update/optimize the reportTransformations in Power Query - Very Slow Performance
Best Regards
Hi Anonymous ,
Which connection mode you are using to connect SQL Server? Import or Direct Query? Is the underlying database running smoothly? It is difficult to give a suitable solution without sample data and related execution operations. Because there are many possible reasons(large amount of data, specific applied steps etc.)... You can try the following operations anyway and see if the situation has improved later:
1. Upgrade your Power BI Desktop to the latest version
2. Uncheck background data (File –> Options –>Data Load –>Allow data previews to download in the background)
Any way to speed up really slow refresh times in Power Query?
3. Refer the following blog to update/optimize the report
Transformations in Power Query - Very Slow Performance
Best Regards
For those that still find this difficult to navigate, you may be doing this in Power Query from Analysis Services.
- Added Items to a query then filtered query.
I did and a clever soul in my organisation found this solution to stop us maxing SQL server resources which made queries run for up to an hour for relatively simple requests.
The issue with this can be the complexity and the size of the data warehouse that you are querying. The issue
occurs at the added items step where the query is building the relationships within a large dataset when you request fields from different tables.
Our solution is to use MDX as the first step. For those that can't build these natively and that includes me, install SQL Server Management Studio and browse to the Analysis Services cube. You can then drag and drop the fields you want to use as well as filter at this step in a manner similar to pivot tables in Excel so very user friendly.
Once you have this code from SSMS which you can get in Design Mode, launch Power Query and follow the steps below.
- Alt + F12 to launch power query editor.
- New Source | Database | Analysis Services
- Server is your server address, Database is your Analysis Server model name and MDX or DAX query is the code you generated in SSMS.
We're finding the time difference is in the thousands of percent better by comparison.