Forum Discussion
list.number Act weird
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
Community ChampionHello 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
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
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
JimmyThanks 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 Anonymous
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