Forum Discussion
Combing thousands of excel files using Power Query. Is my workflow correct?
Hi,
I've built a power query function to extract data from excel files. The excel files are in the same format. The files are stored on a Onedrive folder. I've successfully queried the data using get data > sharepoint folder and then filtering and then invoking the custom function to extract the data from columns to rows. In the end I get data from each excel file to be a row. This works.
However, it takes quite some time to refresh this data and the source will grow quite a bit. Right now we are looking at over 2000 excel files. Is there another option I should be looking for to speed this process? I've built it such that a master excel file is doing the power query and then I'm using that as the source for a Power BI dashboard that allows me to look across all the data. How should I go about to speed up this process and making it scaleable? Help. Thanks in advance.
Hello Anonymous
exactly, I only added Table.Buffer
So give it a test and let us know
Jimmy
11 Replies
- Hello
The approach is all right. If you want you can post your custom function (and if possible a excel file), then i can have a look on this. But reading 2k excel files will take some time. By the way... how long does it take to refresh? Another suggestion is, to use a csv file instead, because they can easier be accessed. But i don't know if this could be a option for you.
All the best
JimmyI'm not sure a custom function is needed to do this aggregation. If you are simply appending the files to each other, this is a built-in function. It may be faster to append all to one dataset and then group the results.
I have a blog post on how to use the out of box merge process here: https://marqueeinsights.com/how-to-merge-multiple-excel-files-with-power-bi/
Hope this helps.
--Treb, Power BI MVP
Thanks.
This is the master template that is being used. I'm extracting the indicator table (C14:E33). I have the function write the province, district, and facility and then pivot the indicators to columns for each excel file so I get a master table with each row representing each excel file.
It takes about 20-30 minutes to refresh.
Is CSV a feasible option?
Should I be doing this query in Power BI rather than have the excel doing the power query?
Would it be possible to autoamte the refresh without having to open the excel file and udpate the query?
- Hello
Could you please post the m code from the advanced editor?
Jimmy
Hello Anonymous
have you been able to solve the problem with the replies given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
JimmyHi Jimmy801 ,
Thanks for the follow up.
My code has changed a bit since I last posted, but wanted to confirm that the only change was adding "table.buffer" at the following section:
Daily_Data_Entry_Sheet = Table.Buffer(Source{[Item="Daily_Data_Entry",Kind="Sheet"]}[Data]),if so, i can test it on my current query function.
Thanks again,
Tony
Hello Anonymous
exactly, I only added Table.Buffer
So give it a test and let us know
Jimmy
