Method and Performance optization in Power query

Dico

Member
Joined
May 23, 2020
Messages
41
Reaction score
0
Points
6
Excel Version(s)
2019 Pro Plus
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
 
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
 
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
 
The workbook would be more helpful than a PDF.
 
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.
 
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.
 
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
 
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.
 
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!
 

Attachments

  • PowerRequete.xlsm
    29.3 KB · Views: 13
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.
 
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/0...ormance-in-power-bi-desktop-using-table-view/
 

Attachments

  • PowerRequete2.xlsm
    146.7 KB · Views: 13
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
 
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.
 
Back
Top