Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi I'm new to Power BI and I have a question if any one can help.
I basically want to know if my order was delivered in between the Date and Slot time provided by the customer.
Id | Order Created Date Time | Delivered Date Time | Slot Date | Slot Time |
1943069 | 01/09/2023 02:34 | 01/09/2023 15:10 | 01/09/2023 | 09:00 - 12:00 |
1943070 | 01/09/2023 02:39 | 01/09/2023 20:00 | 01/09/2023 | 18:00 - 21:00 |
1943071 | 01/09/2023 02:42 | 01/09/2023 16:50 | 01/09/2023 | 15:00 - 18:00 |
1943073 | 01/09/2023 02:48 | 01/09/2023 11:06 | 01/09/2023 | 09:00 - 12:00 |
1943074 | 01/09/2023 02:51 | 04/09/2023 13:44 | 04/09/2023 | 12:00 - 15:00 |
1943075 | 01/09/2023 02:53 | 04/09/2023 13:43 | 04/09/2023 | 12:00 - 15:00 |
1943078 | 01/09/2023 03:00 | 01/09/2023 20:39 | 01/09/2023 | 18:00 - 21:00 |
1943079 | 01/09/2023 03:03 | 02/09/2023 13:46 | 02/09/2023 | 15:00 - 18:00 |
1943080 | 01/09/2023 03:05 | 01/09/2023 13:57 | 01/09/2023 | 15:00 - 18:00 |
1943082 | 01/09/2023 03:09 | 01/09/2023 13:51 | 01/09/2023 | 12:00 - 15:00 |
Very straightforward, #date & #time produces #datetime
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJbCsQgDAXQrYjfHZqnVbdSuv9tjHaEIQkz9Ks00NMbr+eZsQlDaXnLgDu0nYA4AXUWO0LtCGY0X1oHSK+ENJ752hZ3QOTcHwjmF47D+uEILYeBE3LpStfI6UpXLceRq44bAcrTZSVwegeWL8ddxIxmOlqcWk4jx5Hjp5zbDNif+6zCtfOnCn9RBndHIZOumNHvKqq/KINz+w9Oj4fNVopcixwGzp7d9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Order Created Date Time" = _t, #"Delivered Date Time" = _t, #"Slot Date" = _t, #"Slot Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Order Created Date Time", type datetime}, {"Delivered Date Time", type datetime}, {"Slot Date", type date}, {"Slot Time", type text}}),
Check = Table.AddColumn(#"Changed Type", "Check", each let span = List.Transform(Text.Split([Slot Time], " - "), Time.From) in [Slot Date] & span{0} <= [Delivered Date Time] and [Delivered Date Time] <= [Slot Date] & span{1})
in
Check
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hi
another solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJbCsQgDAXQrYjfHZqnVbdSuv9tjHaEIQkz9Ks00NMbr+eZsQlDaXnLgDu0nYA4AXUWO0LtCGY0X1oHSK+ENJ752hZ3QOTcHwjmF47D+uEILYeBE3LpStfI6UpXLceRq44bAcrTZSVwegeWL8ddxIxmOlqcWk4jx5Hjp5zbDNif+6zCtfOnCn9RBndHIZOumNHvKqq/KINz+w9Oj4fNVopcixwGzp7d9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Order Created Date Time" = _t, #"Delivered Date Time" = _t, #"Slot Date" = _t, #"Slot Time" = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Order Created Date Time", type datetime}, {"Delivered Date Time", type datetime}, {"Slot Date", type datetime}}),
AddOnTime = Table.AddColumn(ChType, "AddOnTime", each
[Delivered Date Time] >= [Slot Date] + Duration.From(Text.Start([Slot Time],5))
and
[Delivered Date Time] <= [Slot Date] + Duration.From(Text.End([Slot Time],5)),
type logical)
in
AddOnTime
with "Slot Date" = type datetime and not type date
Stéphane
Hi @maqboolk51
Give this a go
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJbCsQgDAXQrYjfHZqnVbdSuv9tjHaEIQkz9Ks00NMbr+eZsQlDaXnLgDu0nYA4AXUWO0LtCGY0X1oHSK+ENJ752hZ3QOTcHwjmF47D+uEILYeBE3LpStfI6UpXLceRq44bAcrTZSVwegeWL8ddxIxmOlqcWk4jx5Hjp5zbDNif+6zCtfOnCn9RBndHIZOumNHvKqq/KINz+w9Oj4fNVopcixwGzp7d9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Order Created Date Time" = _t, #"Delivered Date Time" = _t, #"Slot Date" = _t, #"Slot Time" = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Order Created Date Time", type datetime}, {"Delivered Date Time", type datetime}, {"Slot Date", type date}}),
AddOnTime = Table.AddColumn(ChType, "OnTime", each
( Date.From([Delivered Date Time]) = [Slot Date] ) and
[ t= Time.From([Delivered Date Time]),
s= List.Transform( Text.Split([Slot Time], " - "), Time.From ),
r= s{0} <= t and s{1} >= t
][r], type logical
)
in
AddOnTime
returns this result
I hope this is helpful