Data modelling with Power BI requires attention to performance and efficiency. One key step is optimising your query in Power Query.
How to Optimise Query Size in Power Query
Optimising query size is one of the first steps to improving Power BI performance. Here are practical strategies for reducing size and load time.
Use Power Editor to understand how many tables are involved and their column/row counts.
- Eliminate unnecessary columns to reduce memory usage.
- Filter the data to limit the number of rows processed.
- Change granularity by aggregating numeric values when appropriate
- Set proper data types for each column.
Push Data Preparation to the Source System
- Whenever possible, perform data transformations at the source level. For instance, if you’re using SQL Server, preparing data using views, stored procedures, or ETL tools like SSIS or Azure Data Factory is more efficient.
Organising queries in Query Editor
- A helpful way to improve code organisation is to group all queries with their respective objects.
Understanding data type conversion
- The conversion of data types can impact data modelling. Although the Power Query engine treats all the numeric values as numbers, they are compressed differently depending on their column cardinality after loading the values in the Power BI Model.
- It is essential to set the correct type of facet for each column.
- Use the Fixed Decimal datatype (faced) for numeric values when possible.
- Take a datatype that makes sense to the business and is efficient in our data model.
- Include the datatype conversion in a step when possible: the more transformation steps we have, the slower the data refresh will be.
Optimising the size of queries
- Optimising query sizes can reduce the data refresh time. A model with an optimised size performs better after we import the data into the data model.
- As previously reported, we are removing unnecessary columns and rows.
- Fewer columns mean less memory consumption and, as a result, a more performant data model.
- Power BI gets an error when the size limit is exceeded.
Summarise tables: Group by
- Summarising tables is one of the most effective ways to keep the model size more optimised and performant.
- By digesting the data, we change the granularity of the data to a higher level.
- We should discuss data summarisation with the business.
- It is advisable to use the Group by feature to summarise the tables.
Disabling query load
- It’s a good idea to disable query load from the Power Query Editor to optimise data transformation in Power Query. This will prevent unnecessary data load and ensure smoother operations.
- Having naming conventions for Power BI developers and data models is essential. This helps with solution consistency and makes the code more readable.
Written by nf, 2024
