Forum Discussion

Suresh_Jayaraman's avatar
Suresh_Jayaraman
Copper Contributor
Jan 15, 2021
Solved

Slow Power Query

Hello,   I am new to Power Query. I have a sample file with 3 Power Queries. The query takes a considerably long time to load the data, irrespective of no. of rows in the table. I have less than 10...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 15, 2021

    Suresh_Jayaraman 

    In your file there are few thousands of links on external files which are in hidden name. On Refresh Excel tries to update them and it takes time. Using FindLink (manville.org.uk)  I delete all of them.

    As an example

    hidden Name	BExCUSR1TLT2YXF5OVHCGT9D0CIS		='\\PHCHBS-S3047.EU.NOVARTIS.NET\SAULJA2$\DOCUME~1\bullimi1\LOCALS~1\Temp\N.notes.data\[FX effect LE2  Q3 September 13.xls]Table'!$I$11:$J$11	Deleted

    In addition I'd recommend to set Ignore privacy setting for this file, you don't need it with this data structure.

    With that Power Query itself works relatively fast.

    Another story is that approach you use doesn't work. You add manual columns with some data, but they won't be in sync with the main table returned by PQ. You may change sorting of such table and refresh - values in additional columns will be on another positions. To resolve you need to query such table and merge with main one on unique ID within same query. 

    Attached is the file in which removed everything except sheets with queries, you may check how it works.

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 15, 2021

Suresh_Jayaraman Have looked at your queries and believe you omitted a few important steps in your description of what you are doing. After loading a table back to Excel, you manually expand it (adding columns with data and formulae) before querying it again. And that you do two more times. Not sure that this is "best practise". I suspect that this is causing your problem.

Then you also have all your queries set to refresh upon opening the file. That hung-up my Excel for quite a while. When I changed that setting, the file opened smoothly. Though, the queries still took long to open/review/edit.

Resources