Question on the use of List.Dates function

[Smile]

Hi all, 

I was wondering if there is an option to use List.Dates function with only the week days (excluding Saturdays and Sundays)?

Thanks in advance!

[v-yalanwu-msft · Accepted solution]

Hi, @Smile ;

You could try it.

=List.Select(List.Dates([Starting Date],
[days]+List.Count( List.Select(List.Dates([Starting Date],[days],#duration(1,0,0,0)), each Date.DayOfWeek(_,1)>=5)),
#duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5)

The final show:

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyMjAy0jXUNQUyTZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Starting Date" = _t, days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Starting Date", type date}, {"days", Int64.Type}}),
    Custom1 = Table.AddColumn(#"Changed Type", "eachDate", each List.Select(List.Dates([Starting Date],
[days]+List.Count( List.Select(List.Dates([Starting Date],[days],#duration(1,0,0,0)), each Date.DayOfWeek(_,1)>=5)),
#duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5))
in
    Custom1

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Smile]

Hi Yalan Wu, 

Thank you so much for the suggestions, it worked perfectly!

[Smile]

Hi Vijay,

Thank you so much for your quick reply 🙂

The query works well but when it was placed in the condition where I was listing dates for the set amount of time specified in another column, for example,

- Person A took 5 working days off starting from Jan 5 2022 (Wednesday), which is represented in the original data set as below:

Name	Starting Date	Working days off
Person A	5th Jan 2022 (Wednesday)	5

- the 5 days off would be 5, 6 and 7th of Jan (Wednesday - Friday) and 10th and 11th of Jan (Monday - Tuesday next week), which should be transformed as below:

Name	Starting Date	Working days off	Each date	working day off
Person A	5th Jan 2022 (Wednesday)	5	5th Jan 2022 (Wednesday)	1
Person A	5th Jan 2022 (Wednesday)	5	6th Jan 2022 (Thursday)	1
Person A	5th Jan 2022 (Wednesday)	5	7th Jan 2022 (Friday)	1
Person A	5th Jan 2022 (Wednesday)	5	10th Jan 2022 (Monday)	1
Person A	5th Jan 2022 (Wednesday)	5	11th Jan 2022 (Tuesday)	1

Instead of listing 10th and 11th Jan as the last 2 working days off out of the total 5 working days, I was not able to find the last 2 records after the transformation was applied. This makes me think that the query assigned 8th and 9th Jan (Saturday and Sunday) to the last 2 working days off and removed these 2 records as they fall in weekends.

#"Added Custom.2" = Table.AddColumn(#"ConverttoDate", "eachDate",each List.Dates(List.Select([date],[days],#duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5))

This is the query I've applied where [date] = the first day off and [days] = duration of time off in days.

Could you please advise if there is anything I can do to rectify this issue? 

Thank you so much for your help!

[Vijay_A_Verma]

List.Dates won't allow but it can be paired up with List.Select to select only weekdays. See below example where I am generating 100 dates starting 1-Sep-22 and selecting only week days.

= List.Select(List.Dates(#date(2022,9,1),100,#duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5)