Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iplaygod
Resolver I
Resolver I

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

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
    //---------------
1 ACCEPTED SOLUTION
iplaygod
Resolver I
Resolver I

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

power-query-custom-function.png

View solution in original post

3 REPLIES 3
Sepp
New Member

Hi guys,

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

Best regards,

Sepp

@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
Resolver I
Resolver I

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

power-query-custom-function.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.