Custom Functions in Power Query

Peter079
Posts: 2
Joined: Mon Jul 24, 2017 7:58 pm

Custom Functions in Power Query

Postby Peter079 » Mon Jul 24, 2017 8:18 pm

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 ?

Any help would be greatly appreciated

Peter

MattAllington
Posts: 887
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Custom Functions in Power Query

Postby MattAllington » Tue Jul 25, 2017 7:30 am

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.

I hope my article here will help demystify functions for you https://exceleratorbi.com.au/combine-ex ... -method-1/
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Peter079
Posts: 2
Joined: Mon Jul 24, 2017 7:58 pm

Re: Custom Functions in Power Query

Postby Peter079 » Tue Jul 25, 2017 7:13 pm

Thanks Matt

As always you come up with an answer that makes sense


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest