Forum Discussion
Slow Power Query
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 DeletedIn 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.
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.
I cleaned up, added XLookup's, and attached FNS+2.xls. I will keep this for now and work on the approach you mentioned.
With the help of another friend, I also found an article that explains the approach you mentioned,, the link is here :
https://social.technet.microsoft.com/Forums/en-US/e1c18dfc-fff2-4621-96a6-bd1a7a3903f4/how-can-i-add-a-new-column-to-the-workbook-so-data-can-be-entered-in-addition-to-the-results-loaded?forum=powerquery
Yes, that's an idea. There are few blogs which describe the approach, I'm not sure who first introduced it.