Slow Power Query

[Suresh_Jayaraman]

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 rows. 

Any help on why the slowness is much appreciated. I have attached the file

More Info: The queries are rather easy to read but I will explain them anyway.

1st Power query loads data from Table1, Removes certain columns, and Loads the remaining data into Table2 

2nd Power Query loads data from Table2, Removes certain columns, and Loads remaining data into Table3

3rd query does similar steps to the above.

I am not doing complex operations or transformations, yet the power query is slow.

PS :

I did check my system RAM and I have 8GB on a Windows 10 HP laptop.

I don't have any issues with loading other power queries so system slowness is ruled out. 

I tried other steps like "disabling change type", "Fast data load" etc but no help

[#2]

@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.

[#3 · Accepted solution]

@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.

[#4]

@Sergei Baklan 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-mvDEGmk8CzLi19SknnbwpiOw...

[#5]

@Sergei Baklan 

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...

[#6]

@Suresh_Jayaraman 

Yes, that's an idea. There are few blogs which describe the approach, I'm not sure who first introduced it.