Power Query Connection only

gazmoz17

Board Regular
Joined
Sep 18, 2020
Messages
158
Office Version
  1. 365
Platform
  1. Windows
Hi,

Im a newbie and not sure if my query takes so long due to source data being slow (ODBC Sage connector) or If Im doing something wrong?

Originally I had all my queries loading onto tabs, however I have changed this now so source queries (apologies for wrong terminology...I mean initial queries I have merged into main query) are just connections. Should this have speeded up the loading of my Merged query.......the fact there only now connections feeding into my main query?...or does it not necessarily work like that.

I have a 2nd query returning to excel and then do a vlookup ref 2nd query to main. Would it be faster to do this in power query and only have this second query as a connection?

Highlighted yellow = subquerys
Glass Pivot (main query)
SAS (2nd query lookup source)

Many Thanks
Gareth
 

Attachments

  • Queries.PNG
    Queries.PNG
    3.9 KB · Views: 13
  • Main query steps.PNG
    Main query steps.PNG
    13.5 KB · Views: 13

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You might need to show the different steps from the Advanced Editor screen in order to see more about what is going on with your issue.

However, refresh speed is associated to both the source (your ODBC), your network/internet connection, and the amount of data that is being pulled down through the ODBC.

If it is pulling a lot, then yeah...time will be an issue. Best tip, eliminate columns and rows that are not needed as soon as they are not/no longer needed. And then when you merge queries, only expand your desired columns as opposed to the whole incoming query.

To answer your VLOOKUP question, it could go either way. I typically do my lookups in Power Query so I'm not bogged down by formula calculation time. But it also adds more time to the Power Query refresh.
 
Upvote 0
Before merge queries, you want to reduce to the smallest amount of columns possible (remove unneeded columns). That is the single performance improvement I would guess is applicable here.
 
Upvote 0
Hi guys thanks for the input there. So vlookup in query or in excel may or may not make it quicker either option.

I'll revisit and see if I can remove rows etc before merge.

*Is there a way in settings to measure query speed and maybe establish if its ODBC related, internet related or query steps itself?

Many thanks
 
Upvote 0

Forum statistics

Threads
1,223,574
Messages
6,173,141
Members
452,501
Latest member
musallam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top