How to optimise query size in Power Query?
- We can use Power Editor to know how many tables are involved and how many columns and rows they have.
- Eliminate all unnecessary columns.
- Filter the data to reduce the number of rows.
- We may consider changing the granularity of some tables by aggregating numeric values.
- Treat the data types properly.
Push the data preparation to the source system when possible
- For instance, when we are connecting to a SQL Server data source, it is best to take care of all transformation steps on the SQL Server side by creating views, stored procedures, or tables populated by ETL (Extract, Transform and Load) tools such as SSIS (SQL Server Integration Services) or Azure Data Factory.
Organising queries in Query Editor
- A helpful way to improve code organisation is to group all queries with their respective objects.
Understanding datatypes conversion
- The conversion of data types can have an impact on data modelling. Although the Power Query engine treats all the numeric values as the type of number, they get 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 it is possible: the more transformation steps we have the slower the data refresh will be.
Optimizing the size of queries
- Optimizing query sizes can reduce the data refresh time. A model with an optimized 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 get an error when exceeding the size limit.
Summarize tables: Group by
- Summarizing tables is one of the most effective ways of keeping the model size more optimized and more performant models.
- By digesting the data, we change the granularity of the data to a higher level.
- We should discuss data summarization with the business.
- It is advisable to use the Group by feature to summarize the tables.
Disabling query load
- To optimise data transformation in Power Query, it’s a good idea to disable query load from the Power Query Editor. This will prevent unnecessary data load and ensure smoother operations.
Disabling query load
- Having naming conventions for Power BI developers and data models is essential, and it helps with solution consistency and makes the code more readable.