Creating linked tables - Power Query VS direct links (=A1) - Is either better/faster?

Hi all,

I have a few spreadsheets which are all based on one huge SOURCE table. This table is populated with data from an SQL database which is loaded by a VBA script.

From this source table - I've built two additional sheets which in turn both are pretty big.

The purpose of this is that I want to create new tables which contains specific information I'm interested in from the source table AND perform a few new calculations using standard excel formulas.

Procedure:

1) Create new sheet with new table

2) Create links from source table to new table by simply using the formula "=" on the top cell in new table, find top cell in desired column in source table and click enter. Repeat until table is complete.

Simply like this - except that I have tables.

Now, this is working FINE, but on my largest workbook consisting of two linked sheets with additional formulas - I have some issues with stability. At times - it can load pretty fast. Other times - it tends to freeze a bit.

Question is - Is PowerQuery a better solution to accomplish this? Is it likely to be faster/more stable?

It will likely take me an entire day to re-create all this. Maybe more. So I figured I could ask here first.

One advantage with a direct link is that my entire workbook is updated at once. Of course, that can be accomplished in PQ as well using VBA.

Thanks in advance for any pointers on the subject!

Best regards,

Elijah

I just did a test now and a PowerQuery linked table does seem faster. I will add a lot of formulas though in addition to color formatting - so I won't be able to know until I eventually complete it.

Would still appreciate a comment on this.

If I go with PowerQuery:

a. Can I still perform calculations using standard excel formulas in the actual table as opposed to in PowerQuery? I've done this on my other PQ tables and it seems to work fine.

I'm resurfacing this one, please.

Moderators: Is it possible this one is more suited in the General or some other section of the forum?

I'm leaning towards actually re-writing this in PowerQuery and simply testing it for myself, but would still like the input from the great people on this site who surely knows a lot more about this stuff than me.

Thanks.

Elijah

Hi, Dave,

I do understand. And I did not mean to be impatient. I was mostly concerned that maybe I had put this in the wrong section of the forum.

As for the question at hand - I have not yet been able to find the time to start on this project, so would still appreciate a general opinion on this.

Thanks in advance.

Best regards,

Elijah

Hi, Ali,

I have the same experience. Never was a trouble for me until now (if PQ is the reason at all).

Do you have an opinion on the question I'm asking with regards to direct link with = formula vs PQ to build a table from another one?

Thanks.

Another advantage with PQ is of course also that my source table grows in size and I may load data sets with various # of rows. With PQ - this auto-adjusts the size of the table. On direct links - I need to make sure the table has the sufficient # of rows as it does not auto-adjust relative to the source table.

How am I quoting you unnecessarily? I was specifically adressing a portion of what you wrote and not your entire text. Seems quite in its place to quote you on that. Makes the conversation easier to follow for all parties and all readers, IMO.

Hi, Derwood726,

Thank you for your suggestion!

I wanted to quote you, but it seems like people ain't allowed to quote 'unnecessarily' on these boards...

To answer your question (and here, a quote would have been really nice and to the point):

No, I have not thought about that. I did not even know it's a possibillity.

It does seem very interesting, though.

Would it be difficult to set up something like this? Would it be fast and stable? I have absolutely zero knowledge on Access.

The data in the SQL database is created by a C# application. I'm not 100% sure, but I wonder if there's possibly some calculations happening inside Excel as well on the source table. I can't say for sure though. I don't fully understand the architecture of this design as a company made it for me many years back now.

Really thanks for coming up with this suggestion. I have not yet started the time consuming task of rebuilding these workbooks, so I may very well consider this as an option. The question is if I would be able to do it myself in a reasonably amount of time OR hire someone to do it for me.

Best regards,

Elijah

You can export the SQL straight from the source with some simple script in an access table setup in same format as your sql table or use access's import features to import/link the sql. Both are fairly simple.

Access queries are a very powerful tool for custom data output needs from large sources and pretty simple to use/create. Setting parameters within these queries for your desired output is far less complex with access than excel as well. No long index/if/match/offset functions to write out....

Excel can create linked tables from these created access queries that run just as smooth as working in a regular excel table.

It all really just depends on your needs and setup/permissions with your sql server. A lot of people tend to shy away from access as it can seem a bit more complicated and not as user friendly as excel but excel can't match it's querying tools especially if you're dealing with large amounts of data and multiple tables/sources.

Derwood,

Once again - thanks! You've got my thinking here and it seems like Access can be a wonderful solution indeed. Due to me being very behind on things and having had a lot of technical issues lately, I think I will have to mark this one down as something I will consider not 'today', but in the weeks to come as I can find some more time to look into it properly.

Since we're on the subject - is there any need to export things to Excel when working in Access? Can Access substitute Excel entirely?

Originally Posted by Derwood726

You can export the SQL straight from the source with some simple script in an access table setup in same format as your sql table or use access's import features to import/link the sql. Both are fairly simple.

I tried quickly to set this up yesterday, but got stuck. Will try again as soon as I'm able to find the time.

Thanks again, Derwood.

Elijah

Question is - Is PowerQuery a better solution to accomplish this? Is it likely to be faster/more stable?

I just performed a test right now:

1) Workbook with zero linked tables, i.e., only main table - Loading time (SQL DB) = 4,0 seconds

2) Workbook with 2 linked tables - Loading time = 4,9 seconds

3) Workbook with 2 linked PowerQuery tables = 8,0 seconds

So, the Power Query variant is basically half the speed of my direct links.

And these are simply PowerQuery tables mirroring the original table with no additional calculations. Adding calculations - probably even slower.

So, I probably answered my own question here.

Originally Posted by AliGW

There is no point in using PQ for exact mirroring. You only use it if you need to transpose or otherwise manipulate the data.

Maybe. I can see 2 benefits though:

1. If the source table is dynamic - your PowerQuery table will update accordingly, i.e., number of rows.

The way it is with my current direct link tables, I have a linked table with excessive rows below (to accomodate for a growing source table).

2. You can choose to update the source table without updating the linked table.

Anyway, regardless if one is only mirroring or not - my question was if either has advantages or if PowerQuery is more efficient/stable than direct links.

Based on my test - it seems like direct links are far more efficient.

Elijah

Jeez. I hope I can vent a little. Sometimes Excel or computers in general are a mystery to me.

a. I have a workbook with 2 linked tables that for some reason had become very slow upon loading data (VBA which loads data from SQL into Excel).

I rebuilt this workbook from scratch. That is - I removed both linked tables and rebuilt it with one linked table instead since the two linked tables had a lot of 'overlapping' data. The expected result would be a faster load time and a smaller workbook, right? That's what happened.

b. I have a similar workbook also with 2 linked tables. This one loads pretty fast at around 5 seconds.

I decided I wanted to rebuild this in the same fashion as I did with the workbook above to have one similar format on both and most likely improve speed.

Spent all day doing this and thought it would end up really efficient.

Trying to load data now and Excel freezes and takes around 30 seconds just to load the data. So, basically all my work today is wasted and I will just have to go back to what was already working.

I have NO idea why.

Any takers?

Hi again, all,

I'm putting the 'solved' temporarily off as I would appreciate if anyone have any opinions or theories to share on what I'm experiencing.

So, see post #26 above.

This morning, I decided to recover an earlier save of this workbook from yesterday and quickly retraced my steps leading to the final workbook similar to what I had yesterday. Now, here's what's puzzling me:

Today, the load time is 15 seconds! That's half of what I had yesterday on the similar end result. No other changes.

What on earth can cause such differences? As I'm pretty sure there is NO difference between the two workbooks?

Still, the original workbook at 5 seconds remains the fastest one, so I'm probably just going to have to go back to that one anyway.

I can illustrate what I've done with a picture. See below. Originally, I had 1 source table and 2 linked tables.

For the 2 tables, Part A was similar for both linked tables. So, Linked Table 1 = Part A + Part B. Linked Table 2 = Part A + Part C.

What I've done here with this new workbook is to simply add Part C to Linked Table 1 and removed Linked Table 2 entirely. Should be faster, right? But it's not at all...

Just wish I understood why.

2.png

Originally Posted by Elijah

Unless it's the sheer total size of this new linked table which is the main problem and that splitting them up would help regardless of the total being greater?

Okay. This is interesting (please don't remove the quote, Ali, as it is necessary).

On this new workbook which takes 15 seconds to load I decided to split this new linked table in two such that it's similar to the original workbook:

The result: Load time = 10 seconds , i.e., 5 seconds faster than 1 linked table.

How can this be?

The only answer I can think of is that total table size per table is what matters.

It still does not explain why my new workbook is 6 seconds slower than my original as the content is the same...

I also tried to remove all conditional formatting from the entire workbook and it amounted to pretty much nothing.

I did a test on the conditional formatting on the original table. It seems to only reduce load time by 0,5 / 1,0 seconds. So on that workbook - it's less demanding than one might think.