Hi, I asked the same question at the Powerpivotpro forum but no response. My question is I have 8 accounting databases all set up in the same way (rows and columns). I have read a bit about custom functions in power query and its going over my head. The data does need a bit of work done to it before loading it into a data model. My question is does it matter whether I append all the data upfront (approximately 60 000 rows of data in total) and then carry out the transformations on the appended data set, or should I create a custom function in Power Query on one of the databases and then invoke the custom function to import the other 7 databases. There won't be any additional databases added in the future. Is there any difference between the 2 methods ?

In effect it doesn't matter - there are many paths up a mountain. Personally I would only ever create a custom function if there is a need to reshape the data before it is combined. An example would be if you had 5 rows of garbage at the top of each of 10 spreadsheets. It would be easier to remove the top 5 rows from each sheet rather than combine the sheets and then try to find the extra rows randomly down the table.

In your case it sounds like no such issues exist. If the starting point is 8 identical tables that will append well, then you can append first and transform later. One thing to be aware of is query folding. If the query is fast, then it doesn't matter. If it becomes slow, then it could be that your query is doing too much work in power query and not enough in the database. At each step of your query, right click the query step and select "native query". If this is greyed out, then query folding has stopped and all transformations will be done in the client and not the database. Try to keep query folding going as long as possible by varying the order you do things. Some can be folded, some cannot.