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
Hi guys,
next step: Can I reuse parts of M code across several pbix files? In the sense of a library?
Best regards,
Sepp
- iplaygod7 years agoResolver 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