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/

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/