I have some Excel files that use power query to get data through an internal ODBC connection. I need to distribute the files to other computers in the business. Data security isn't a concern because all of the computers operate on the internal network only. The computers in question are quite old and there's not much chance of replacements coming soon. I've got 2 options in terms of constructing the queries: either do it in VBA with data manipulation happening there (I'd rather avoid this option), or do it all in PQ which I'm more familiar with. What I'm not sure about is if these options have the same load on memory or if one will reduce it substantially. Processing capacity is also a concern, but not as big a problem as memory. I know VBA will load everything into memory when it executes and then dump it all once the sub routine finishes. Does PQ hold all the query tables in memory all the time, or does it only load them while the query executes and while it's being edited?
I've had files built both ways, but haven't had the time to build the same functionality twice to test properly.
By default, Power Query streams data rather than load it all into memory at once. There is an excellent video here which discusses the inner workings:
https://www.youtube.com/watch?v=AIvneMAE50o