Forum

Speed of Processing
 
Notifications
Clear all

Speed of Processing

5 Posts
3 Users
0 Reactions
89 Views
(@hallo-ich-bins)
Posts: 4
Active Member
Topic starter
 

Hi I am a newbie an try to use Power Query to substitute the vlookup (sverweis) in excel.

I have a table with about 20000 (it is a join of nearly 15 tables) entries and filter/calculate them. Every time I open excel query the computer is calculating something about 5 minutes long. Is that because of the amount of entries or could I do something better.

Thanx

Jörg

 
Posted : 24/09/2021 12:40 pm
Alan Sidman
(@alansidman)
Posts: 223
Member Moderator
 

Check your connections and see if you have Excel refresh the query upon opening.  If so, you can turn it off if you don't need it to refresh.

 
Posted : 24/09/2021 3:57 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Jorg,

You can also ensure background refresh is turned off in the query options. Query editor File tab > Options and Settings > Query Options > Current Workbook > Data Load > Background Data: deselect 'Allow data previews to download in the background'.

I don't expect this will affect the file opening time, but it should improve working in the Query Editor.

Mynda

 
Posted : 24/09/2021 6:45 pm
(@hallo-ich-bins)
Posts: 4
Active Member
Topic starter
 

Thanx to all of you. That worked.

Now it is possible to view the datas without having a coffee.

This topic is solved.

 

Regarding the speed. Could there be an advantage of using the data modell or should I use this only for Power Pivot?

 

Greeting

Jörg

 
Posted : 26/09/2021 2:26 am
(@mynda)
Posts: 4761
Member Admin
 

Glad we could help 🙂

Power Pivot is the data model, so I'm not sure what you mean exactly. Perhaps this video on Power Query vs Power Pivot will help.

 
Posted : 26/09/2021 6:04 am
Share: