Forum Discussion
Power Query Performance with Custom Functions applied to Excel Workbooks
What is the purpose of opening the template file for every file? Why not just make 4 functions, each to extract the table from each workbook (Details, Finance, etc.). I also wouldn't do the Table.Range and Table.SelectRows in the function, but would do that after the tables are combined from all templates.
If you do need something from the template file for each file, I would pull extract that in your final query and wrap it in Table.Buffer( ) so it only get called once. If you can't do without the template file, using Table.Buffer will be key to decrease refresh time significantly (avoid doing it 4 X 263 times).
Pat
Thanks for your thoughts.
I like the idea of not opening the template file for every file, and there isn't anything I need from the template, but I don't know how to write the custom function without reference to it. I based what I've got so far on what I read here: https://docs.microsoft.com/en-us/power-query/custom-function
(I didn't use one of the files I'm importing as the template as these will change over time)
Also, I don't understand how I could not use the Table.Range and Table.SelectRows in the function. If I don't use them in the function don't I just end up importing the whole sheet?
