cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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.N.List.PNG

 

appreciate your help. 

1 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

View solution in original post

7 REPLIES 7
Super User III
Super User III

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

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

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

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 .

 

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

View solution in original post

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.... ‌‌😎

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors