Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Method and Performance optization in Power query

  1. #1
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus

    Method and Performance optization in Power query



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

    Hi,

    I'm new to this forum and this is my first post and I am proud to join this forum.
    I am French and please excuse me if I make mistakes in my explanations or text.

    For this problem, I would need your advice on the method used and performance optimization in Power Query.

    I have prepared a document with more details of the project that I have finalized.
    Unfortunately, the refresh of the data in power query is rather slow.

    Is this the right method with tables containing folder and file paths ?
    Is there a better solution for this kind of project ?

    You'll excuse me if I don't answer quickly because of the time difference with France.

    Thank you in advance.

    Sincerely

  2. #2
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Do you have a query for each folder containing your excel workfiles? If so, you can try loading it as a folder, create a table of nested excel files and then expand on them

  3. #3
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Hi masterelaichi,
    Thank you for your response,

    I combined all the files into one table using a custom function.
    I imported my path table in Power query (see example on page 2 of my document) and then combined the files using the function

    My goal is also, to present the final request according to the desired month.
    If all files have been combined at the end of May, I may wish to see my aggregates at any time at the end of February or end of March only.
    My method works but will be too slow when I arrive with the 12 months in my request

    Do you please have an example, a web link, a video or an article on the method you propose ?

    Thank you in advance

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    The workbook would be more helpful than a PDF.

  5. #5
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Hi Bob Phillips,

    Thank you for your message,
    You're right, an example file will be easier to understand the method used

    I'll build a file without personal data and as close as possible to the real context.
    It's going to take me some time.

    I'll come back as soon as I finish the file.

    Thanks again.

    See you soon.

  6. #6
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Hi,

    Here I am again with an example file and a tutorial.

    Is this method effective or is there a better solution?
    This is only a small part of my final project.

    Thank you in advance for your advice

    https://we.tl/t-zli3LwzIbu

    Have a nice day.

  7. #7
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Quote Originally Posted by Bob Phillips View Post
    The workbook would be more helpful than a PDF.
    Hi,

    As wished I put a file and a video as a bonus (see my post #5).
    I haven't had any answer for 3 days.

    Nobody has an idea if there is a better method than the one on the video?
    Thank you in advance

  8. #8
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Don't know if this will help but at 4:45 or so on the video you add a custom function to a table with 12 rows, and then filter the table down to 2 rows. Could you filter first? The function appears to be looking for files in a directory so in this particular case you'll only need to search 2 directories instead of 12. Of course this won't speed things up if you want 12 months of data.

    And at 5:15 you have a single column, "Content", with the binary data. What happens if you expand that column? I don't know if that will allow you to get at the data you need more efficiently.

  9. #9
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Downloaded your zip file and answered my own question about expanding the binary data. Got an error when doing so that I couldn't resolve, even with the help of Google. I made the change to filter for the months of interest before querying for the files. Can't tell if it made any difference since it refreshes quickly. I also removed a couple of steps where you were deleting columns, and simply selected the column with the data near the end. Don't know if that will help, either.

    I linked the folder path parameter to a table on the worksheet with the current file path. You'll probably get an error and will need to change the privacy setting in Power Query to "ignore" for this to work. Also added some visual basic code to automatically refresh the query when you change the month selector. Learned the French word for query when it didn't work as expected!
    Attached Files Attached Files

  10. #10
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Hi NormS,

    Thank you very much for your interest in my post. There are some good comments like this one
    Don't know if this will help but at 4:45 or so on the video you add a custom function to a table with 12 rows, and then filter the table down to 2 rows….etc
    .

    Otherwise, I also have a macro for auto-refresh on my real file.
    And the parameter for the dynamic path in cell B3, I'm also familiar with it.

    Let's imagine that the Selection column didn't exist, what other method could I use to get to the result of the video ?
    I added this column because I had no other choice. But I'm convinced that there must be another solution.

    As I said in my first messages, my project contains many more queries and steps. Maybe the performance is to be found in the other queries.

    I will continue with this method if there is no other choice and I will open other posts for advice and opinions.
    I am convinced that in the all of my project there are optimizations and good ideas that will come out on this forum.

    Thanks again to you

    Have a nice day.

Page 1 of 2 1 2 LastLast

Posting Permissions

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