Page 2 of 2 FirstFirst 1 2
Results 11 to 13 of 13

Thread: Method and Performance optization in Power query

  1. #11
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus


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

    If you didn't have the selection column you could load all of the data, and then create a pivot table to analyze it. I queried all of the files in the main folder, filtered the ones of interest, combined them, loaded it to the data model and created a pivot table with slicers to illustrate, if you haven't already tried this.

    Are you familiar with Chris Webb's blog? He often discusses ways to improve performance of Power Query and Power BI. This is a recent post that also links back to some earlier discussions of performance issues.

    https://blog.crossjoin.co.uk/2020/05...ng-table-view/
    Attached Files Attached Files

  2. #12
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Hi NormS,
    Thanks for the alternative with Pivot Table.
    Unfortunately, I can't apply a pivot table at this level because it's only the beginning of a long project.
    The final request is the result of a merge and many other manipulations with other sources.

    I know Chris Webb's blog very well and it's one of my favorites along with the Ken Puls blog...and many others.
    Thanks for the link, I'm going to look at this article I haven't read yet.

    I will continue step by step my project with videos on this forum. Maybe optimizations will come for each step.

    Thank you

  3. #13
    Acolyte Dico's Avatar
    Join Date
    May 2020
    Posts
    26
    Articles
    0
    Excel Version
    2019 Pro Plus
    Quote Originally Posted by NormS View Post
    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.
    Hi NormS,
    I added your VBA code to automatically refresh the table and change the filter order (see this video).
    This is a good idea, I think it can only get better.
    I'll continue my project step by step, I'll open new posts to get advice and opinions from the community.

    Thanks to you

    Have a nice day.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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