Ignoring Blanks in Power BI Query Invoke Custom Function

Question

I'm using the following code in a Blank Query in Power BI Desktop:

(InitialDate as date, FinalDate as date ) as number => 
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates), Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays

It's code to calculate the working days (that is, excluding Saturday and Sunday) between 2 given dates. I use it using the Invoke Custom Function when adding a column to the query, as shown below:

Invoke Custom Function

The problem is that, when one or the two dates are empty (blank) the Invoked Function returns Error. See below:

Error

How can I change the code in order to ignore the cases where one or the two dates are empty/blank, and just return blank?

If not possible, is there a way to make the same function in DAX?

Thank you!

Answer 1

you can first check if you have valid Dates and use this in if statements, then you can ensure to give a value to the null dates for your calculation (even when you do not use them)

let
    ValidDates = FinalDate <> null and InitialDate <> null,
    InitialDate = if InitialDate = null then DateTime.LocalNow() else InitialDate,
    FinalDate = if FinalDate = null then DateTime.LocalNow() else FinalDate,
    DaysBetweenDates = if ValidDates then Duration.Days(FinalDate-InitialDate) else 0,
    ...
    WorkingDays =if ValidDates then (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList) else null
    in

ps did not test the code

Answer 2

Power Query has error handling. Maybe this is how you can solve this.

Here is about error handling https://docs.microsoft.com/en-us/powerquery-m/errors

You can just do something like this

DaysList = try List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates), Duration.From(1)),
WeekDaysList = if DaysList[HasError] then <handle this somehow> else List.Select(DaysList[Value], each (Date.DayOfWeek(_, Day.Monday) < 5) ),

Answer 3

I'm using this exact code which was found from Youtube: https://www.youtube.com/watch?v=bs3yzmf9elA

In the comments the creator advised how to do exactly what you are asking:

1. Invoke Custom Function normally
2. When the formula bar has processed the function, add "try" right after the word "each"
3. Add "otherwise null" right before the final parenthesis

in creators case, the function is called Query1. The code is basically checking if there is an error in the formula and returning null instead before giving you the final output

= Table.AddColumn(#"Removed Columns1", "Query1", each try Query1([Initial], [Final]) otherwise null)

The above code worked for me to help handle missing dates. But I noticed the calculation for the days isn't taking holiday's into account. Which is what I'm working on getting sorted now! haha.

Hope this helps!

Answer 4

I had the same problem. You need to accept the parameter not to be specified by type and assume it will be date otherwise null:

    (InitialDate, FinalDate ) as number => 
        
    if InitialDate = null or FinalDate = null then 0

    else
let
       DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
    DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates), Duration.From(1)),
    WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
    WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
    in
    WorkingDays