Forum Discussion

Smile's avatar
Smile
Frequent Visitor
4 years ago
Solved

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!

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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)

     

  • Smile's avatar
    Smile
    Frequent Visitor
    4 years ago

    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!

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Smile
      Frequent Visitor
      3 years ago

      Hi Yalan Wu, 

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