Speed/Performance aspects

This is a collection of tips how to improve performance of your M-queries. It will be updated continuously.

Disable background refresh

I turn this off in general. It slows down many queries considerably. As you see, I’m also disabling almost everything else, as most if it can also have a negative impact on performance.

See Chris Webbs post for more details: https://blog.crossjoin.co.uk/2016/12/05/power-query-power-bi-and-the-allow-data-preview-to-download-in-the-background-option/

Partition your table for vertical operations where possible

If you do calculations in the vertical direction (like summing up multiple values from one column) for each row, performance can degrade drastically. If these calculations shall only be made within a certain sub-group, you can speed up the process significantly by grouping first, so that the vertical scan only has to go over the subsets of the table: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/ Also check in these cases, if a DAX measure would be an even the better solution here: https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

Use Table.Buffer or List.Buffer

where appropriate. This is a bit of trial and error, but generally speaking: Use it when your table or list will be referenced multiple times (like in List.Generate).

See again a post from Chris Webb for more details: https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buffer/

Improve query speed during design-phase

Use a filter-toggle to de-/activate multiple filters in your tables at once: http://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

Your SQL-queries are slow?

Check if query-folding happens and if you are filtering with non-SQL-data, you might have to adjust your queries: http://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquery-excel/

Grouping on contiguous data?

Change the fourth parameter in Table.Group to “GroupKind.Local”. This is an optional parameter will not be present when you create your group-table via the UI. So you have to manually insert that parameter like so:

Table.Group(#"Sorted Rows", {"StartDate"}, {{"All", each _, type table}}, GroupKind.Local)

Documentation says: ” A local group is formed from a consecutive sequence of rows from an input table with the same key value. A global group is formed from all rows in an input table with the same key value.”

Check your sources

If you have the choice between an import from csv or Excel, take the csv, as it will also be much faster. Excel files have a much more complex format, store much more (background/meta) information and are therefore slower to read.

Disable privacy settings

If this is feasible in your organisation, you can also disable privacy settings to improve performance. Please read more details here: http://datapigtechnologies.com/blog/index.php/four-must-know-power-query-options/