Editing a Power BI Desktop report can be productive, satisfying, and even fun when the data it is using is quick to edit and refresh. As our data model grows and the transformations we perform on it become more complex we may find editing, saving and applying them can take forever, whittling all that joy away.
Let’s have a look at the many ways we can resolve slow refreshes of our queries and put the power back into ‘I’ve got the Power BI’…:
Getting it Right Before Getting Data
Your connection – If your data is being loaded over a slow connection this will impact refresh times; get a better connection, use a datamart, or run Power BI on a virtual machine as close to the data source as possible.
Your machine – In Task Manager quit any unnecessary processes that might be impacting power query’s, get a more performant computer, run your query in the Power BI Service as a datamart, or run Power BI on a virtual machine.
Datamarts and Dataflows – Consider using a datamart or dataflow, especially if you’re reusing the same transformed data across multiple reports. This can help centralize data preparation and improve overall performance.
Data source optimization – Consider optimizing your data source before importing it into Power Query. This could involve creating appropriate views, indexes, partitioning large tables, or pre-aggregating data where possible.
Power BI Settings
Disable Power Query background refresh – In some cases, disabling background refresh can speed up the development process. Go to File » Options and settings » Options » Data load, and uncheck ‘Allow data preview to download in the background’.
Parallel loading – Enable parallel loading for multiple tables to speed up the overall refresh process. Go to File » Options and settings » Options » Current File » Data Load, and check ‘Enable parallel loading of tables’.
Optimising your Tables in Power Query
Query folding – Remove columns then add filters before any other steps to leverage query folding – careful as some data sources aren’t compatible with this feature, you may need to run native query folding instead.
Merging – This can increase refresh times extensively, especially when aggregating data. Avoid by splitting out tables into a star/snowflake schema, try filtering on a list using List.Buffer and List.Contains instead of merging, and ensure any query folding steps (remove columns, filters) are applied to the source query before performing the merge.
Incremental refresh – This will keep historic data intact and only refresh the most recent data specified in your parameters. Check out avoid the full refresh from the inspiring Guy in a Cube channel.
Loading – Disable the loading of queries that aren’t being used to create visualisations in your report by right-clicking the query in the left panel and unselecting ‘Enable load’.
Leverage Power Query parameters – Use parameters to make your queries more dynamic and easier to maintain. This can also help with performance by allowing you to quickly adjust filters or data ranges without having to edit the table in Power Query.
Transformations, calculations, adding columns – Depending on the complexity of the transformations these will slow refreshes down. Often it can’t be helped, and there is the option to use DAX instead. Careful though as this pushes the processing to your users, so test to ensure their experience isn’t unduly impacted. Also, the learning curve is relatively high once you move past the ‘quick measure’ options. Rule of thumb : If it can be done in both DAX and Power Query, use Power Query.
Referencing queries – Queries ‘referencing’ other queries will run twice if they’re both being loaded. This may slow refreshes down, use a datamart instead. Learn more about this See referencing queries on learn.microsoft.com.
Custom functions – Be cautious with custom functions, especially those that process rows one at a time which can significantly slow your queries down. Get them to operate on entire columns or tables if possible.
Artificial intelligence – Click on ‘Advanced properties’, copy/paste the Power Query mashup (M) into your favourite AI, and ask it to suggest performance improvements. Use a prompt such as:
I am using the following M code in Power Query and refreshing the data takes far too long. I’d like you, as a power query expert, to review the code and make changes to it so it runs faster and refreshes do not take so long. Where it will increase performance please include suggestions for splitting the table out into other queries, using List.Buffer or List.Contains, adding incremental refresh, ensuring query folding is leveraged wherever possible, and any other power query functionality that might improve the performance of the query. Please add a line break and commentary before each step so it is clear what each is doing. Please rename each line of M code to something meaningful and use CamelCase for the name.
[paste your M code here]
Be sure to save your original power query code in a text file; then replace it with the AI’s response. Copy any errors and ask your AI to fix them…
Checking and Maintenance
Monitor and analyse query performance – Use free tools like DAX Studio, Model Documenter, or the Power BI’s inbuilt Performance Analyzer to identify any bottlenecks in your queries and help focus your optimization efforts.
Regular maintenance – Periodically review and clean up your queries. Remove any unnecessary steps, combine operations where possible, and ensure you’re not keeping redundant queries.