Excel Forum

Excel performance very slow because of a lot of Power Query tables

jaryszek

Member

Members

Forum Posts: 183

Member Since:
February 1, 2019

Offline

June 17, 2021 - 6:40 pm

hi Guys,

i have about 250 tables imported into excel worbkook using power query.
It means about 250 queries created.

What i have noticed after inputting data Excel is slow, has a small delay. But this is annoying and not seems nice to user.
When i deleted power queries performance is awesome.

Why this is happenning and how can i improve performance?
I can not delete queries because i am importing tables once again.

Please advice,
Jacek

See Answer

Mynda Treacy

Admin

Forum Posts: 4614

Member Since:
July 16, 2010

Offline

June 18, 2021 - 10:26 am

Hi Jacek,

Well, 250 queries is a LOT! I'd try to find a better approach than creating 250 separate queries. Assuming you're then consolidating these queries into one or multiple tables, I would use Get Files > From Folder.

Mynda

jaryszek

Member

Members

Forum Posts: 183

Member Since:
February 1, 2019

Offline

June 18, 2021 - 3:20 pm

Thank you Mynda.

What is the maximum number of queries you did in Workbook?

Unfortunately they are entirely different. I can not merge them.
Why NET.Framework behaves like that?

Best,
Jacek

Mynda Treacy

Admin

Forum Posts: 4614

Member Since:
July 16, 2010

Offline

June 18, 2021 - 7:31 pm

So to be clear, the output is to close & load 250 separate tables?

I'm not sure there's a limit to the number of queries as such and I don't understand NET.Framework to comment on why it's performing the way it is, but like I said, 250 is a lot so it seems reasonable for it to be slow.