Power Query Refresh Time

[bluetaurean]

I have a workbook with 20+ Power queries for sourcing data. It takes over 10minutes to refresh. How can i find out which particular query is causing the issue? is it possible to log via VBA or any other technique the time taken by each of the queries for refreshing.

 

[GraH - Guido]

As far as I know, there is not much one can do with VBA when it concerns PQ. Though I just googled some... and things have moved on, people got smarter.

I see 2 quick alternatives:
- refreshing each query manually if possible (connections only, don't refresh on manual command)
- recording a macro where you call one the queries, then time start and end to know the duration. Loop through all queries.

 

[Chihiro]

There are few things you can do here.

1. Is your data source optimized? I.E. Are you performing data folding on the fly? Or bringing in denormalized "View" or Table from DB?

If former, consider using Stored Proc or View and query it instead of performing complex folding on the fly.

2. How are your query dependency? Does one or more of query depend on other query connections?

If so, first test each query's performance by making each query independent and running each query.

3. Are you using SQL Native query to bring in data?

Check your query plan in SQL.

4. Is this realtime data? Or daily/historical data?

If latter, consider scheduled refresh overnight using task scheduler (if comp can be left on overnight). Or manual refresh, once per day etc.

Without other info, on data sources and how your query plan is structured. Can't say what may be causing the bottleneck.

EDIT: Oh and have a read of link. It's excellent article on Query Folding and how it can improve query performance.
https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/