How to Create Custom Function in PQ Editor

[Txtcher]

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.

[Akash_Varuna · Accepted solution]

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
    CustomFunction

After this you can invoke the custom function by adding a custom column with function like this 

= CustomFunction([Start Date], [End Date])

[Txtcher]

One more quick question:  How would I add to the function that I want the column type to be Int64.Type? (If possible.)

[Txtcher]

Works like a charm! I will be sure to keep the syntax for writing custom functions handy. 

Thank you so much!