Load time of updated source files in Power Query (Desktop Power BI)

[mafin]

I've got a power bi desktop file about 4GB in size and 83 queries and about 10 data sources (about half .csv and the other half .xlsx) (I need data sources to be in .xlsx as I have scripts that overwrite the existing file and can't do this in .csv.)

When the source files are changed, the load when I close and apply is over an hour and still going as we speak!

I need to change these 10 data sources at least weekly and have multiple copies of this file so any load time multiplied out is going to take too long. I need the load time to take 10 minutes max. I'm now having doubts that this is even possible!

Is there a hierarchy on how to build a query most efficiently? I.e. is it best to filter ASAP in the applied steps in order to save load time? Also, am I better to use List.PositionOf() as vlookup or table merge? Which is most efficient?

I've looked online and can't find sufficient help on this. Am I better to do most of the data manipulation in excel? This presents its own issues with updating. Is PQ in PBi designed for complex data manipulation?

I've already reached out to Fiverr and while the person I found helped a lot with the refresh time in the report view (by using DAX), little or no work was done on optimizing the queries in PQ.. 

Any help would be greatly appreciated. 

[watkinnc]

Let us have a look at your code. You might just need some basic changes.

-- Nate

[mafin]

Thants Nate. As a regular user, how do I send you the file as an attachment?

[johnbasha33]

Hi @mafin 

i would do below techniques to optimize the performance.

1. Reduce Data Sources: If possible, try to minimize the number of data sources. Consolidate similar data sources where applicable.

2. Filter Data Early: Apply filters or remove unnecessary data as early as possible in the query steps to reduce the amount of data being loaded into memory. Use filter conditions directly in the source step or as early as possible in subsequent steps.

3. Remove Unnecessary Columns: Remove columns that are not needed for analysis. This reduces the amount of data loaded into memory and improves query performance.

4. Optimize Data Types: Ensure that data types are correctly set for each column. Incorrect data types can lead to unnecessary data conversions, which can impact performance.

5. Reduce Number of Steps: Try to minimize the number of transformation steps in your queries. Each step adds overhead, so combining multiple transformations into a single step can improve performance.

6. Use Table.Buffer(): Consider using Table.Buffer() to cache intermediate results in memory. This can improve performance by reducing the number of queries sent to the data source.

7. Optimize Merge Operations: When merging tables, use appropriate join types (e.g., left join, inner join) to minimize the size of the resulting table. Also, consider using Table.Buffer() on large tables before merging to improve performance.

8. Use Native Query Folding: Leverage native query folding whenever possible. This allows Power BI to push transformation operations back to the data source, reducing the amount of data transferred over the network.

9. Split Queries into Separate Steps: Break down complex queries into smaller, more manageable steps. This can make it easier to identify and troubleshoot performance bottlenecks.

10. Use List.PositionOf() vs. Table Merge: In general, List.PositionOf() can be more efficient than table merges for simple lookup operations, especially if the lookup table is small.

11. Consider Pre-Aggregation: If your data allows it, consider pre-aggregating data before loading it into Power BI. This can reduce the volume of data and improve query performance.

12. Use Query Diagnostics: Enable query diagnostics in Power BI Desktop to identify performance bottlenecks and optimize your queries accordingly.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!