Results 1 to 5 of 5

Thread: Speed up M Code and/or improve techniques

  1. #1
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,952
    Articles
    0
    Excel Version
    365

    Speed up M Code and/or improve techniques



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

    In the attached is a Power Query solution to getting all the combinations of 6 columns of numbers.
    The original problem is from msg#27 at http://www.vbaexpress.com/forum/show...l=1#post407983
    To keep the file small I've deleted the results which will need refreshing; it takes several minutes (more than 10).
    [I realise the results don't fit on the sheet, and it's easy enough to split that table which I'll do later]
    My questions:
    1. Will this benefit from the likes of Table.Buffer statements? If so where should they be?
    2. The code I've used may be awful; is there a better way?

    For cross checking, there is a macro solution included which takes about 5 seconds to put the 6.25 million results into an array, and a further 40 secs to write them to a sheet.

    Ancillary question: Is there a way to get a vba-created array into Power Query without writing to the sheet first?
    Attached Files Attached Files
    Last edited by p45cal; 2021-03-14 at 01:00 PM.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,952
    Articles
    0
    Excel Version
    365
    A 6 week bump…

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    120
    Articles
    0
    Excel Version
    Office 365
    It took me a moment to understand the task correctly.

    Then I developed 2 solutions, which are shorter from the number of steps, but not faster. In both variants I also use Table.Buffer. In the 2nd variant I have divided the query. But this has rather a negative effect on the runtime. I reduced the test set a little bit, so that the runtimes became bearable at all.

    At the moment, it looks to me that Power Query does not come close to the runtime of the VBA solution in this case.
    Attached Files Attached Files

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    120
    Articles
    0
    Excel Version
    Office 365
    I did some more tinkering. As a result, I have a query that expands from F to A instead of A to F and a query where I formed groups (A/B, C/D, E/F) and then connected them. The run times for both these and your original variants are pretty much identical. Whereas by enabling the "Enable fast loading of data" option in the properties of the queries, I have now arrived at about 14 seconds for all variants. I am not yet satisfied with this, but compared to before, this is a very clear improvement
    Attached Files Attached Files

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,952
    Articles
    0
    Excel Version
    365
    Thank you for this pinarello, in some ways I'm glad to see my M-code isn't too bad (although I haven't used Table.Buffer at all). Interesting to note that you get a great increase in speed with "Enable fast loading of data". I couldn't get quite the same speed increase, but worse, when I did a refresh after changing one or two values in the source tables the first refresh still took an age!
    I'm a bit disappointed that PQ takes such a long time; I'd expected it to be faster than vba.
    I've not given up on this yet though…

Posting Permissions

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