M Power Query: how can I reuse a function between multiple queries?

[iplaygod]

Hi!

I have created a custom function that is written into the top of my Power Query M code, and that I use in the transformations of the table in that query.

But I would like to reuse the same function in another query, without having to rewrite the same function and copy paste it into the other queries M code.

How would I do that?

Could I define a separate generic function somewhere in the query editor, that I then can reuse in whatever queries I create? That would be great.

Or can I somehow get access to the custom function written into queryA, from queryB ?

Thanks in advance

This is the function I have written into the M code for a query I have

    //custom function to convert dateTime values to Stockholm timezone    
    convertUTCDateTimeToStockholmTZ = (dateTimeValue) =>
                let 
                    convertedValue =        
                          if dateTimeValue >= DateTime.FromText("2016-03-27T02:00:00") and dateTimeValue < DateTime.FromText("2016-10-27T03:00:00")
	then
		//summertime (+2 hours from UTC)
		dateTimeValue + #duration(0,2,0,0)
	else if
                dateTimeValue >= DateTime.FromText("2017-03-26T02:00:00") and dateTimeValue < DateTime.FromText("2017-10-29T03:00:00")
	then
		//summertime (+2 hours from UTC)
		dateTimeValue + #duration(0,2,0,0)
	else if
            dateTimeValue >= DateTime.FromText("2018-03-25T02:00:00") and dateTimeValue < DateTime.FromText("2018-10-28T03:00:00")
	then
		//summertime (+2 hours from UTC)
		dateTimeValue + #duration(0,2,0,0)
	else if
                dateTimeValue >= DateTime.FromText("2019-03-31T02:00:00") and dateTimeValue < DateTime.FromText("2019-10-27T03:00:00")
 then 
	//this is summertime, 
	//add +2 hours to get Stockholm summer time
	dateTimeValue + #duration(0,2,0,0)
else
	//this is wintertime
	//add +1 hours to get Stockholm winter time
	dateTimeValue + #duration(0,1,0,0)
                in 
                    convertedValue,
    //end custom function
    //---------------

[Sepp]

Hi guys,

next step: Can I reuse parts of M code across several pbix files? In the sense of a library?

Best regards,

Sepp

[iplaygod]

@Sepp 

yes, to some extent you can.

By using dataflows in the Power BI service.

A dataflow is kind of like a power query script saved online in the cloud.

that means you essentially save a Power Query script in the service and then you can re-use it in many different Power BI desktop files by doing "get data" -> Dataflows and selecting your dataflow entity

a dataflow can connect to "local" databases by using the on premises gateway service

[iplaygod · Accepted solution]

Ok so I figured it out 🙂

I go to the left pane where the queries are

right click

new query -> blank query

copy pasted my function code from above

REMOVED the name of the function from the code, letting only the parenthesis + parameter remain at the top

 (dateTimeValue) =>
                let 
                    convertedValue =        
                          if dateTimeValue >= DateTime.FromText("2016-03-27T02:00:00") and dateTimeValue < DateTime.FromText("2016-10-27T03:00:00")
	then
		//summertime (+2 hours from UTC)
		dateTimeValue + #duration(0,2,0,0)
	else if
                dateTimeValue >= DateTime.FromText("2017-03-26T02:00:00") and dateTimeValue < DateTime.FromText("2017-10-29T03:00:00")
	then
		//summertime (+2 hours from UTC)
		dateTimeValue + #duration(0,2,0,0)
	else if
            dateTimeValue >= DateTime.FromText("2018-03-25T02:00:00") and dateTimeValue < DateTime.FromText("2018-10-28T03:00:00")
	then
		//summertime (+2 hours from UTC)
		dateTimeValue + #duration(0,2,0,0)
	else if
                dateTimeValue >= DateTime.FromText("2019-03-31T02:00:00") and dateTimeValue < DateTime.FromText("2019-10-27T03:00:00")
 then 
	//this is summertime, 
	//add +2 hours to get Stockholm summer time
	dateTimeValue + #duration(0,2,0,0)
else
	//this is wintertime
	//add +1 hours to get Stockholm winter time
	dateTimeValue + #duration(0,1,0,0)
                in 
                    convertedValue

Then i changed the name of the query itself to be the name of the function

convertUTCDateTimeToStockholmTZ

All other queries in this Power Bi project can now reuse that function. Great!

Here is an explanation