M code - NetworkDays

Power Query
1

Hi DNA Team,

I thought to calculate working days in m code I will need to use one maybe two m-codes.

I manage to find the formula on youtube after 30 minutes;

(InitialDate as date, FinalDate as date ) as number =>
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, 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

I tried to split each section to have a better understanding. I will appreciate if you could confirm if my thinking is correct ish:

1.(InitialDate as date, FinalDate as date ) as number =>
// This part of the function will be used in Add Columns → invoke functions

  1. DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
    // works as DateDiff calculate the number of days between two dates

3.DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
//This function creates a list from itial date to final date and adds 1 to days between (for example 16/10/2020 - 16/10/2020 will equals 1 day)

4.WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
//indentifies weekends if weekday is greater than 5

5.WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
//List.Count(WeekDaysList) counts how many weekends days there are

I am a bit confused about where the m code formula calculates the working days.

For Example:
05/10/2020 - 16/10/2020

10 workings days, I am trying to find in the m code where the formula do calculation 12 -2 =10

I will appreciate your help DNA Team

2

Hi @Matty,

Here’s a forum topic on the subject with a link to Imke Feldmann’s original post that includes a break down of the M function

.
@Brian also did a video on it which you can find here

If you have additional questions please let me know.

1 Like
4

Thank You @Melissa Could you please load PBI file:

5

Hi @Matty, PBI file of the tutorial can be accessed in the Enterprise DNA Portal