Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Smile
Frequent Visitor

Question on the use of List.Dates function

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!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1663225071864.png

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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1663225071864.png

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.

Hi Yalan Wu, 

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

Smile
Frequent Visitor

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:

NameStarting DateWorking days off
Person A5th 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:

NameStarting DateWorking days offEach dateworking day off
Person A5th Jan 2022 (Wednesday)55th Jan 2022 (Wednesday)1
Person A5th Jan 2022 (Wednesday)56th Jan 2022 (Thursday)1
Person A5th Jan 2022 (Wednesday)57th Jan 2022 (Friday)1
Person A5th Jan 2022 (Wednesday)5

10th Jan 2022 (Monday)

1
Person A5th Jan 2022 (Wednesday)511th 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
Super User
Super User

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)

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors