Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am attempting to generate a list of working dates between start and end dates. I am getting an error, but it doesn't give me a code, can someone please point me in the direction of my error?
= Table.AddColumn(#"Changed Type1", "Dates_Test", each let
StartDate = [DA_EXPECTED_START_DATE],
EndDate = [DA_EXPECTED_END_DATE],
WorkingDays = List.Dates(StartDate, Duration.From(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
FilteredWorkingDays = List.Select(WorkingDays, each Date.DayOfWeek(_) <> 0 and Date.DayOfWeek(_) <> 6)
in
FilteredWorkingDays)
Solved! Go to Solution.
Change Duration.From to Duration.Days
Also you can replace Date.DayOfWeek(_) <> 0 and Date.DayOfWeek(_) <> 6) with Date.DayOfWeek(_,1)<5
Hence, your code will become
= Table.AddColumn(#"Changed Type1", "Dates_Test", each let
StartDate = [DA_EXPECTED_START_DATE],
EndDate = [DA_EXPECTED_END_DATE],
WorkingDays = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
FilteredWorkingDays = List.Select(WorkingDays, each Date.DayOfWeek(_,1) < 5)
in
FilteredWorkingDays)
Change Duration.From to Duration.Days
Also you can replace Date.DayOfWeek(_) <> 0 and Date.DayOfWeek(_) <> 6) with Date.DayOfWeek(_,1)<5
Hence, your code will become
= Table.AddColumn(#"Changed Type1", "Dates_Test", each let
StartDate = [DA_EXPECTED_START_DATE],
EndDate = [DA_EXPECTED_END_DATE],
WorkingDays = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
FilteredWorkingDays = List.Select(WorkingDays, each Date.DayOfWeek(_,1) < 5)
in
FilteredWorkingDays)