list.number Act weird

[AlaaRad]

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. 

[Jimmy801]

Hello @AlaaRad 

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

[AlaaRad]

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]

Hello @AlaaRad 

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

[AlaaRad]

it does calcualte correctly. i didnt understand the source of the query though

can we convert it to function with two paramters( deliverd date and transit date)?

thanks alot for the efforts .

[Jimmy801 · Accepted solution]

Hello @AlaaRad 

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

[AlaaRad]

Thanks heaps ,, you are a legand.‌‌‌‌😀😉
if you could just give me a little tip on why creating a list didnt work.. what was that to do with decialal would be highly appreciated . Mr Legend.... ‌‌😎

[Jimmy801]

Hello @AlaaRad 

in order to filter for weekends you need to have a list of DATES and not numbers. So better is to start with List.Dates to create such a list. You where also showing in your first post that you used a column (DraftDD) with numbers to create a list of numbers. You would need to transform this number to a date somewhen 🙂

Hope i was able to explain you a little bit

BR

Jimmy