Forum Discussion

iplaygod's avatar
iplaygod
Resolver I
7 years ago
Solved

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
    //---------------
  • 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

3 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      iplaygod
      Resolver I

      Anonymous 

      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