Forum Discussion
How to Create Custom Function in PQ Editor
I am creating 6 custom columns within my power query table. Each will have this expression: Duration.Days([End Date]-[Start Date])
There will also be a lot of if statements added in. First to deal with null dates so I don't get errors, and then outcomes.
Here is the core of what we want (but note I still need to add if stmts for null date values):
varMilestone = Duration.Days([End date]-[Start Date])
If varMilestone <0 then null
else if varMilestone=0 then 1
else Duration.Days(End Date]-[Start Date])
How can I set this up as a function I can invoke to create a custom column and then just plug in my column names? I have never created one before.
TIA.
Hi Txtcher You could try something like this and modify it for your needs
let CustomFunction = (StartDate as nullable date, EndDate as nullable date) as nullable number => let varMilestone = if StartDate = null or EndDate = null then null else Duration.Days(EndDate - StartDate), result = if varMilestone = null then null else if varMilestone < 0 then null else if varMilestone = 0 then 1 else varMilestone in result in CustomFunctionAfter this you can invoke the custom function by adding a custom column with function like this
= CustomFunction([Start Date], [End Date])
3 Replies
Hi Txtcher You could try something like this and modify it for your needs
let CustomFunction = (StartDate as nullable date, EndDate as nullable date) as nullable number => let varMilestone = if StartDate = null or EndDate = null then null else Duration.Days(EndDate - StartDate), result = if varMilestone = null then null else if varMilestone < 0 then null else if varMilestone = 0 then 1 else varMilestone in result in CustomFunctionAfter this you can invoke the custom function by adding a custom column with function like this
= CustomFunction([Start Date], [End Date])
