Forum Discussion
Filtering date/time in power query
Hi simoncotterill ,
You can try the following custom columns. In your environment, you may need to change “Date.From([EventTime])” in the equation to today.
= Table.AddColumn(#"Changed Type with Locale1", "Custom", each if (Time.From([EventTime]) >= #time(17, 0, 0) and Date.From([EventTime]) = Date.From([EventTime])) or (Time.From([EventTime]) <= #time(5, 30, 0) and Date.From([EventTime]) = Date.AddDays(Date.From([EventTime]), 0)) then "InRange" else "OutOfRange")If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
, type logical is part of the whole code and not just of the custom column. If you paste the whole code to a blank query and click the wrench next to added custom code step, you will see formula that's just for that column
let DateToday = Date.From( DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 8 ) ) ), //Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset DateYesterday = Date.AddDays(DateToday, - 1), DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0))) in [EventTime] >= DateTime1 and [EventTime] <=DateTime2
Please see sample query below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVZLbis5DLzKIOtHPFGkSMk7fS8R5P7XmKKdzARx2osIDbfLLJFVxby/v6W0Z0qilNLZOLKS7JRo9pQcb1ZL6e3PW7K/nP9mvP6n3pK+ffx5fxu7lmNjUFtNSAcL9aWLfLvwOWPN035CWe7QlJqhVhTcB0dTKgVVW01p4qHqc9VPaKlJ0tiF8pZJqqg/xDdxO2sO7qV4uYC2nVbXfmh1BnTVDuq4cPM5y5ruiccl4ZNT4hyEJw7plAYIK9qjDsJySVh52uZaSOcCgOemLr1R8bOz2uk92RXhZly5ZbLpTFoa09g+aTZrZbtn13PdYUyQB7jOhUONZhAuBzPFg42fhJlvandsnTJaO4Oc6w7GlUYV/MAUbjI4r+VX2FbQYh2hp44eS25UtQ/ikSbbEW29XGHBksGOgS2hCtu0IAjqaL2iwS3/xtnv2By/LbZo+zbCSJxaB+ey9oEah+wtV1jMFJprTtGa6FUhOZ/DTXiAbi7ryqxrVhGaflCXrdFYNVOuOvbZTbPU67oC6ag+SqY0FmVckTK84bjvXr/VrV+CDM6QIXwUDYOW74IMN+OP62/Y9piv5cZTwbRmR6/ggzEkk5uU7jbs7H2FbW2vPUalzq2R9ixU5+gE1052P2Jbr7AP1SWh8Es4yaiixZTCQFBr+Y1zSV9Y3NfCeyOwcFEDjFgiuIB/1obfMn9iYe1koasZ812NGkfdHr4Aj6e0+Y6tmMJI4fnyEEjEIiVwbaht5xV24Hor7JfvdeHcmG9cOsxoT9n6HXtQxlvkcnihIwQilyu64CCfX2CdXc5B4jBKIOdKRq/yimDffWUr6j/n+63uQoVUUdJDVxqRFffVh//MXt43QjJS3UKYcKOGJsM/KTRZXmFj+lAEvhnz7eVRt9vDg6/7XEMBMV+NXkGTO7AY+n1GoH6JNe8I/7bIHCbWPMO/ULaMU8qZi1d90asCBaXg3EIgsEGOuktjVYDHpyb9f+wtfcVzx5cs2mz5sXslWuW4yMDDsUsodl8vOpWG9UpasQWrD2RPxkV76rysX0AVgTOHVloQJoQxjXqHsnpN+LQgQS1fQFfObMaFHOFPmg7ytTg2Pj48cyNdG19AHctDFYE88jqk+4DwlEULGdlREVv5CoruosP7rsZQxSrUIjGkh58fyrjuML5gEa4eE0L9+8YW9L16yPQFNETn4dsV+L2iQ4kOjoFX5Wk4fuNHxJWRHf8QbJi1QMFIY3QYu1dqmysX+O/oT2j+SrhYNzNCpoSU4SOxqIrDQHg9EfabPKAb6Vd3xtaRBiEgmwiqVmL2IiNzmas9Qf/zrIcYQ8PR5j3u16R9Hluxyy9Vv6D9Hghhu3uvsARD/mXFEscF9BIqmPxhJPIsSApoAB1miCMPz/BB3mP6E7S8fXz8Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EventTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"EventTime", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Filter", each let
DateToday =
Date.From(
DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 8 ) ) ),
//Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset
DateYesterday = Date.AddDays(DateToday, - 1),
DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first
DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0)))
in [EventTime] >= DateTime1 and [EventTime] <=DateTime2, type logical)
in
#"Added Custom"
Hi
Thank you so much for your response.
I have tried adding a custom column with the script provided but I am getting a Token Eof expected error.
When I click on show error, it highlights the last comma in the script. (I've cloured it red so you can see)
let
DateToday =
Date.From(
DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 0 ) ) ),
//Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset
DateYesterday = Date.AddDays(DateToday, - 1),
DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first
DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0)))
in [EventTime] >= DateTime1 and [EventTime] <=DateTime2, type logical)
in
Any ideas how to fix this please?
, type logical is part of the whole code and not just of the custom column. If you paste the whole code to a blank query and click the wrench next to added custom code step, you will see formula that's just for that column
let DateToday = Date.From( DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 8 ) ) ), //Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset DateYesterday = Date.AddDays(DateToday, - 1), DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0))) in [EventTime] >= DateTime1 and [EventTime] <=DateTime2