Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

list.number Act weird

Hi guys,

I am trying to generate a list of date to count number of weekend within a list. I tried to find any predefined function serve the purpose but ended up realising there is no such Function ( please tell me if i am wrong so i get releifed ). 

I decied to create a list of number so i convert them to date then to day  in order to get the count fo weekends .

my issue is list.number or list.dates act really weird. not sure where is the mistakes.

Please refer to the picture to know the issue.

 

Start date in the table is different from the start date in the list.

 

appreciate your help. 

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello Anonymous 

     

    this would be the custom function

     

    (DeliveredDate as date, TransitTime as number)=>  List.Count(List.Select(List.Dates(Date.AddDays(DeliveredDate, (TransitTime*-1)+1), TransitTime,#duration(1,0,0,0)), each Date.DayOfWeek(_)>4))

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

7 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    List.Numbers just works fine. Takes your decimal number and adds 5 times 1.

    Just tell us witch is the date frame (witch is start and end date) where you want to count the weekends 🙂

     

    BR

     

    Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI Jemmy,

      thanks for your reply

      I didnt understand your reply, what you mean it take decilaml number???

      I am trying to get the weekend counts for this period/List (DeliveredDate-TransitTime)  .. DeliveredDate.


      basiclly i want to ensure the time given to delivery is all weekday. if the interval between these two set of dates [deliveredDate] Minus [Transittime] and [DeliverdedDate] have any weekend , then new transittime is created by adding old transit time to the count of weekend.
      dont worry about the business logic. its just i need to understand how list.dates and list.numbers works exactly.

      thanks

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello Anonymous 

         

        try out this solution. It uses List.Dates to create a list of all days, filter it by weekenddays and count the items.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSM7DUMzJQ0lEyVYrVAQoYwAVMwAKGFnABY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DeliveredDate = _t, TransitTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DeliveredDate", type date, "de-DE"}, {"TransitTime", Int64.Type}}),
    CountWeekEndDays = Table.AddColumn
    (
        #"Changed Type",
        "Weekenddays",
        (row)=> List.Count(List.Select(List.Dates(Date.AddDays(row[DeliveredDate], (row[TransitTime]*-1)+1), row[TransitTime],#duration(1,0,0,0)), each Date.DayOfWeek(_)>4))
    )
in
    CountWeekEndDays

         

        Copy paste this code to the advanced editor in a new blank query to see how the solution works.

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy