Forum Discussion
M Power Query: how can I reuse a function between multiple queries?
- 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 convertedValueThen 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
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