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 f...
  • iplaygod's avatar
    7 years ago

    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