Apply Query is taking too long.

[Anonymous]

Hi, 

I was working on the Power BI report and on my other laptop due to RAM limitations, I had to remove 90% of tables but when I click on Apply Query with only 2450 Rows it is taking forever to load.

these both tables have only couple thousand rows.

How can I improve this?

[Anonymous]

Hi @Anonymous,

Did you add any advanced operations in your query tables? They may affect the performance and increase the processing times. (e.g. merge, combine, invoke the custom function, nested looping calculations)

Best practices when working with Power Query | Microsoft Docs
For this scenario, you can try to add the buffer functions to package the advanced operations to reduce processing time and duplicated resource costs.

Chris Webb's BI Blog: Improving Power Query Calculation Performance With List.Buffer() Chris Webb's BI Blog (crossjoin.co.uk)

Nested functions and Table.Buffer() - Exceed
Regards,

Xiaoxin Sheng

[Anonymous]

Not at all, imported the data from Data Source and just deleted remove columns and it took forever and in the end I had to start over

[Anonymous]

Hi @Anonymous,

What type of data source are you working on? Did this data source also provide the interfaces to use data connectors that supported the advanced queries when power bi get data? (e.g. SQL, Oracle, ODBC, Odata...) 

How to Get Your Question Answered Quickly
I think doing these data shaping operations on the data source side when getting they may help to increase the loading performances.

Regards,

Xiaoxin Sheng

[Anonymous]

I am using SQLite database with ODBC data source to import it to Power BI.

[Anonymous]

HI @Anonymous,

You can turn on the advanced operation of odbc data connector to use SQL statement to process on the result table and modify its data structures.

Power Query ODBC connector | Microsoft Docs
Regards,

Xiaoxin Sheng

[lbendlin]

In Power Query use "Diagnose" on the last steps of your queries.  That will create statistics of the transform steps, how long they take and how much data they pull.

Most of the issues are caused by merge transforms.  See if you can avoid those and use the data model instead.