Power Query Connection only

[gazmoz17]

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

 

[johnny51981]

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.

 

[jaeiow]

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.

 

[gazmoz17]

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