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/
Don’t use aggregations in when pivoting
When you want your pivot-operations to run fast, NEVER use aggregations in them. Instead aggregate with a group-operation first: https://www.thebiccountant.com/2020/05/17/performance-tip-speed-up-slow-pivot-in-power-query/
Don’t use aggregations after joins
The UI offers you to aggregate your data after join-operations. This is extremely slow, but there are workarounds: https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-query-and-power-bi/
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).
Also use it WITHIN List.Generate (in the “next”-function like described here)
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/
Check if your sources are loaded multiple times to find out where a buffer might be useful: https://blog.crossjoin.co.uk/2018/07/16/process-monitor-power-query/
Binary.Buffer
might help if you’re connecting to multiple non-folding sources: https://www.thebiccountant.com/speedperformance-aspects/
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/
Add a key to your lookup table
You can speed up your merge operations if you add a key to one of the join-fields if possible: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
Delaying types – option for importing Excel-Workbooks
Chris Webb describes here how the delayTypes-option helps to speed up import of Excel-Workbooks.
Test for the best lookup and indexer operators
If you want to select a specific field from a table, it makes sense to test out the order of your lookup- and indexer operators for best performance like Chris Webb describes here.
Getting Max or Min values from a column
Chris Webb describes performance gotchas here.
Keep things in “table space”
If you want to benefit from potential optimisation, you should keep your query logic in table space. Says Curt Hagenlocher in the comments to this thread.
Referencing previous or next rows?
Referencing previous or next rows in Power Query will only work fast on large datasets if you use the “shifted index”-method that I’ve described here: Fast and easy way to reference previous or next rows in Power Query or Power BI – The BIccountant
Referenced web queries
If you’re pulling data from the internet and reference staging tables, disabling parallel loading of tables might speed up your queries: https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/
Don’t group on nested objects
Expand them first.
Transform multiple columns at once with reference to an existing column
Hire an expert
If none of the above actions work for you or you don’t want to try it all out by yourself, I can offer my consulting services. Contact me: info at thebiccountant dot com