How to write a WorkdayDiff date function in Power Query M Language

Question

I am trying to create a custom function which calculates the workday difference between two dates. I have done the same function in SSRS:

Public Function WorkdayDiff(StartDate As Date, EndDate As Date) As Date

  Return Datediff("ww", StartDate, EndDate, vbMonday) + 
         Datediff("ww", StartDate, EndDate, vbTuesday) + 
         Datediff("ww", StartDate, EndDate, vbWednesday) + 
         Datediff("ww", StartDate, EndDate, vbThursday) + 
         Datediff("ww", StartDate, EndDate, vbFriday)

End Function

I tryed to do the same now in Power Query M language but it keeps me saying:

Type is not defined.

This is my code in the advanced editor in Power BI:

(DateStart as Date, DateEnd as Date) as Date =>

let
    WorkdayDiff = Datediff("ww",DateStart ,DateEnd, vbMonday) +
                Datediff("ww",DateStart ,DateEnd, vbTuesday) + 
                Datediff("ww",DateStart ,DateEnd, vbWednesday) + 
                Datediff("ww",DateStart ,DateEnd, vbThursday) + 
                Datediff("ww",DateStart ,DateEnd, vbFriday)
in
    WorkdayDiff

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-01T13:12:34

Your code has several issues.

The reason you're getting Type not defined is that M is case-sensitive and you need to use date rather than Date when referring to data types. M also does not have a Datediff function or understand what vbMonday means. You have to use M functions rather than just pasting in SSRS code and hoping it works.

Here's a possible implementation in M in the style of this post.

let
    WorkdayDiff = (StartDate as date, EndDate as date) as number =>
    let
        DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) + 1, #duration(1,0,0,0)),
        RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
        CountDays = List.Count(RemoveWeekends)
    in
        CountDays
in
    WorkdayDiff

How to write a WorkdayDiff date function in Power Query M Language

1 Answers