Top 10 Power Query Best Practices

❓ Do you have any best practices you consider when developing Power BI models or reports? 

📒 I’ve recently started making an effort to documenting my own favourite “best practices” that I use whenever I’m developing in Power BI

Here’s my Top 10 Power Query Best Practices 🥇

1. Parameterise!
   Use parameters for any metadata that might change – a good candidate is usually connecting string information for data sources. You can easily change this later and makes updating queries a lot faster!
2. Reuse code through functions or reference queries
   If you need to use the same transformation logic across multiple queries, write a function that can be reused! Be careful with reference queries – the base query will run each time you reference it.
3. Dataflows are your friend
   Consider use of a dataflow where a query is reused across multiple datasets, or is referenced multiple times within a single dataset. This reduces the number of queries and could potentially boost refresh performance.
4. Group those queries
   Make use of a sensible folder structure, for example by data source, query type, table function or even just split out your facts and dimensions.
5. Disable temporary tables
   If you’re not using a query in the data model, disable the load! This reduces the model size and prevents unnecessary queries being loaded.
6. Filter rows to minimise data volume
   Use a date column, or make use of “Keep First Rows” to reduce the data volume whilst you are developing. Parameterise this so you can easily change it later.
7. Minimise table width
   Remove surplus columns to reduce the model size and keep your model lightweight!
8. Ensure query folding where possible
   Query folding improves performance by making the data source do the heavy lifting. You can check if your query is folding via the step indicators: https://learn.microsoft.com/en-us/power-query/step-folding-indicators
9. Remove Other Columns > Remove Column
   The “Remove Other Columns” transformation creates a column selection list, meaning your query dependencies are on the columns you want. The “Remove” transformation on the other hand, creates a column deselection list, meaning your query depends on columns you don’t want.This makes the “Remove Other Columns” transformation the better choice in most cases, because:
   • If a column you don’t want is deleted from source, your query still works
   • If a column you do want is deleted from source your query fails (which is good!)
   • If a column is added to the data source, it won’t be added by default to your query (this is good or bad depending on your scenario)
10. Name your steps!
    Not only does this help separate your transformations and create a logical ordering, it is a huge help to anyone else who might use your model. It makes documentation a whole lot easier in the future too!