Results 1 to 5 of 5

Thread: Inexplicably long runtimes despite small amount of data.

  1. #1
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365

    Inexplicably long runtimes despite small amount of data.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    In the uploaded folder, the days off are marked, but the column does not remain empty. I have just corrected this.
    Attached Files Attached Files

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,023
    Articles
    0
    Excel Version
    365
    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!
    Attached Files Attached Files

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    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/...2-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.
    Attached Files Attached Files
    Last edited by p45cal; 2021-10-23 at 04:39 PM. Reason: corrected link (was 'can't execute code in break mode')

  5. #5
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    146
    Articles
    0
    Excel Version
    Office 365
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •