Solved Inexplicably long runtimes despite small amount of data.

pinarello

Member
Joined
Jun 21, 2019
Messages
214
Reaction score
4
Points
18
Location
Germany
Excel Version(s)
Office 365
I have a Power Query application, with very little data, but it always runs the first 1-3 times, after opening, for a very long time, up to 100 seconds on my desktop, to update the data (work_occupancy_schedule). After that, the runtime is relatively normal.

The recalculation is triggered by pressing the button "Refresh Belegungsplan", or the selection of another calendar week.

The purpose is to create a weekly attendance plan for kindergarten and after-school children ("Kita" and "Hort" spreadsheets). Holidays ("Freie Tage") and temporarily deviating attendances ("Kita temporär" and "Hort temporär") are taken into account.In the list, the children per group are displayed in ascending order according to the planned arrival time and, depending on the determined time of day, are displayed on the left, in the middle or on the right for each day and the minimum number of rows per group is also taken into account.
In the list, the children per group are displayed in ascending order according to the planned arrival time and, depending on the determined time of day, are displayed on the left, in the middle or on the right for each day and the minimum number of rows per group is also taken into account.


In order to achieve the final display, an overall list (query "Liste_Kita_und_Hort") is created after preparation of all data, from which the individual lists are then created and displayed according to the respective color bars.

In principle, it would also be possible without any problem to create the rows for the color bars within the queries in order to then generate the color markings using conditional formatting. But this would have no influence on the too long runtimes, because this is created in one of the upstream queries. But since it is not possible to set timestamps within a query, I unfortunately cannot find out why the whole process runs so long after opening. I also don't have the impression that I am using functions that are known for long runtimes.

But maybe someone, with enough experience, has an idea which command is responsible for the long runtimes, or can give me a hint how to find out myself.

I would like to mention, that this is a question, which was asked by a kindergarten from Switzerland, in a German forum. The link to post is included in the folder.
 

Attachments

  • mof - Wochenplan Kita und Hort - V5.xlsm
    129.4 KB · Views: 4
In the uploaded folder, the days off are marked, but the column does not remain empty. I have just corrected this.
 

Attachments

  • mof - Wochenplan Kita und Hort - V5.xlsm
    129.4 KB · Views: 8
I know no more than you. However it gave me the excuse to play with Ken Puls' Monkey Tools. The attached is a report from same which seems to indicate the Liste_Kita_und_Hort is the problem when Privacy is On…
…now it remains to fathom out what it all means and what to do about it!
 

Attachments

  • ExcelGuru11338MonkeyToolsTeport.xlsx
    17.7 KB · Views: 8
Thank you very much, that helped me a lot!!!

I had the queries by a renewed, complete revision, of number and scope of the M-steps again completely revised and thereby the runtime problem to a third reduce, but just not completely eliminate.

With your hint I googled and found the following page: https://support.microsoft.com/en-us...er-query-cc3ede4d-359e-4b28-bc72-9bee7900b540

With the hints mentioned therein I then made the following change: Open any query in the workbook > File > Options and settings > Query Options > GLOBAL > Privacy > Privacy Levels to: "Always ignore Privacy Levels"

Attached is the workbook with the optimized queries and the changed privacy settings.
 

Attachments

  • mof - Wochenplan Kita und Hort - V7.xlsm
    126.9 KB · Views: 5
Last edited by a moderator:
Since I had never changed anything in the privacy settings before and never had a problem with it, the question remains, why did the problem with the long runtimes occur in this workbook?

Furthermore, I noticed that if I deactivate the privacy setting in the GLOBAL area, then this setting is not passed on to the current workbook when saving. From my point of view this is a bug in Power Query.
 
Back
Top