PowerQuery performance discussion

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Dear All,
Especially the ones more familiar with M code, databases and data analysis. I admit that PowerQuery and M code seem to be quite a versatile tool, but I am puzzled by its low performance. So I wonder - is it me doing something wrong or it's just the way it is. Is there anything I can do about it.

Some time ago I built an excel file to enter data and then show some data extracts & summaries using PowerQuery tables and alter the data when necessary. The basic purpose is keeping track of invoices, payments and accounting. I used VBA, UserForms and M code. Everything is in one file, icluding the data. The file itself is not too large (10 sheets and about 1 MB per 1000 records)
So all is working and no major bugs for more than half an year now. However since the very beginning I am unhappy with the file performance when it comes to refreshing the PowerQuery tables and actually present the necessary data to the user. Because some VBA procedures are run on the output (formatting and arranging data) after the data is updated I do not run the queries in the background (as a side effect this also allows me to easily measure how long a refresh takes).
Since the first production built of the file I managed to learn a bit more about M code, so naturally I tried to shorten, simplify and optimise my queries. Some improvement, but nothing major. Then I changed the source of some queries (where possible) - used the output table from a previous query instead of going through the full data. So this gained some time, but still far from fast and smooth. Used from a shared folder the amount of time a refresh took becomes close to annoying. So despite my efforts it kept on feeling clumsy and slow.
Eventually, last week I decided to try a combination of ACCDB as backend, containing all the data, and the front-end containg the interface (forms, CustomUI, VBA code) and the output tables.
Using ADODB and SQL made the process times faster.
Using a pesistent connection to the ACCDB made the overall process of updating and formatting the data at least 10 times faster then PowerQuery. A single table updates through ADODB and SQL for approx 6 ms, while the same takes more than 300 ms for PowerQuery.
The other slightly annoying bit is that the PQ refresh time is not too consistent each time.
So when you need to update several tables per sheet it makes quite a difference. Not to mention the initial refresh on file opening which may take 15 or more seconds.

So any advices are welcome.
Is there anything I can do, ot it is what it is - sacrificing a bit of speed for the sake of a serious set of functionalities? I found out PQ can do great many unexpected and nice things (e.g. extracting data from PDFs and making sense of it), but a large non native data set (e.g. a 500 pages PDF :eek: ) totally killed it.
 

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"
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,638
Members
430,373
Latest member
swartzfeger

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
Top