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.
SergeiBaklan You are a lifesaver!
I downloaded Findlinks add-on and executed it myself. I wonder what those hidden links are and why would it be there in the temp location. I added the details of where I got the file from at the bottom of this response just in case if you are interested and also other folks who may be reading this response. Please see Gantt_Chart.xls
Approach and design decision:
However, Regarding my approach, you mentioned "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. "..... " To resolve you need to query such table and merge with the main one on unique ID within the same query. "
My Requirement :
The First table under sheet Step1(Plan) is not a static table, it will grow with unique IDs generated on the fly whenever a user adds the row. The column Unique Item ID is to uniquely mark each row with an ID generated on the fly when a user adds the row. My current query loads that unique Item ID's into a table in the Step2(Do) sheet and I further use Xlookup to find additional data from the first table.
In the second table in sheet Step2(Do), my requirement is that the users of this sheet, when they are ready for Step 2 will add more details (for the same UniqueID they added in step 1). They don't want me to combine all the information that is needed in step 2 into the Step 1 table because users of the table will not have all the information during the Step 1 process and they want a minimalistic approach.
So, with the approach, you suggested, I cannot use PQ merge if I don't know the Unique ID's to Merge. I am reading and loading the unique ID from the first table to the second table and use the Xloopup in the second table to find a few other details I need from the first table. In the case of sort and filter changes of the 1st table, My Xloop up should still find the correct data from the 1st table.
I could not think of any other way to know and load the Unique Row that is added in the first table except using PQ and XLookup( i have Xlookup's in the latest version, not in the version I shared with you). Please let me know if you think of any other design approach.
More about where I downloaded the file :
I downloaded the attached file from simple sheets. They have a one-time fee for various excel templates and Gannt charts which we can download and use for your project. I don't know if I need to trust these folks until I know what those links are for.
https://simplesheets.co/?gclid=CjwKCAiAl4WABhAJEiwATUnEF6bYVUpAxrf02FbtCCbt-mvDEGmk8CzLi19SknnbwpiOwOGuXT3hfBoCjEMQAvD_BwE
