Forum Discussion

simoncotterill's avatar
simoncotterill
Regular Visitor
1 year ago
Solved

Filtering date/time in power query

Hi I hope someone can help with a problem I'm having. I have a date/time column in a table showing alarms that I want to filter to only show between 17:00 to 05:30 i.e. overnight. I had it working...
General Comment
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

  • danextian's avatar
    danextian
    1 year ago

    Hi simoncotterill 

    , 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

     

simoncotterill's avatar
simoncotterill
Regular Visitor
1 year ago

Hi

Here is a small sample of the data I'm using. I want to filter the EventTime column to only show alarms occurring between 17:00 and 05:30. Obviously there are two different dates covering those times. 

IDEventTime
00EC0034-00FE-0024-3E00-CA007C00D90006/12/2024 08:04
BE85F6BB-9D93-4B13-AD4D-7E731FFBDCF906/12/2024 08:13
0096002E-00EF-0094-5500-9800C500840006/12/2024 08:13
58030BE5-2E3C-44BB-B37E-19FDCB1A557506/12/2024 08:13
9E0DA4AF-DA1C-4D8A-9D24-97CC5DC7701B06/12/2024 08:13
00F20012-00EC-003A-0B00-49004700830006/12/2024 08:13
41C6E185-4CD0-41CE-A3A9-57FE246FAA0606/12/2024 08:13
99618192-6C71-4591-BE7C-C9695E77274F06/12/2024 08:13
009A001B-00CD-0046-CB00-5F00DB006B0006/12/2024 11:46
8C3B99FB-718E-41C8-B836-CC3193B12DD706/12/2024 11:46
95DA44B4-00AC-4329-84AB-1B0C16F349A506/12/2024 11:46
004100D1-005F-006E-D800-A2004300920006/12/2024 11:47
26F3436D-E7E6-4707-9A36-5DEF31FB3EE306/12/2024 11:47
00E50097-00DB-0045-3F00-49000F009E0006/12/2024 11:47
3C8DC833-C7F6-4169-BD82-284BEFE9423806/12/2024 11:47
00370044-0045-00BD-2200-26007300ED0006/12/2024 11:48
00F5009A-0002-005A-9B00-4C003C00180006/12/2024 11:49
86291C49-B827-478A-BB32-7635A76B6FEE06/12/2024 11:49
99EDEBB8-A199-4A23-8CBA-C50C177F36E406/12/2024 11:49
006B0003-0083-00E6-8900-07008A00580006/12/2024 11:50
0065006C-00B3-00CE-9700-1300FE00820006/12/2024 17:21
00770061-00CB-00D9-9100-0A004600E40006/12/2024 17:21
008D00B0-0035-00DB-7C00-080093006F0006/12/2024 17:21
00B900DD-002B-00DC-C200-8900CB00690006/12/2024 17:21
00FC0079-00FF-00A2-6E00-85007100290006/12/2024 17:21
7173FF85-1069-4D52-972D-3E0EAD26547E06/12/2024 17:21
00D50008-0072-0040-4100-04009200660006/12/2024 17:21
00B2001E-006A-006D-4B00-9E000C00150006/12/2024 17:21
005800E6-00C3-00A5-4100-A6000F006F0006/12/2024 17:21
008E0081-004F-0023-E100-70009300400006/12/2024 17:21
67ACC59D-6733-42C9-BD99-3BF55FCD1D8E06/12/2024 17:21
005A0006-0090-00BA-2100-D4001200220007/12/2024 01:03
00A40068-0062-00FE-3B00-7E00BB00F60007/12/2024 01:03
BE5A54C4-B6A8-483C-87B6-42040A0A1D6A07/12/2024 01:03
4037CB48-DB90-4DC6-AAA2-A80B485F346207/12/2024 01:03
D2216615-7DD7-40F7-9573-A661FCEEE39107/12/2024 01:03
7B834407-B2DF-4EFC-8C3D-D284A462E0D107/12/2024 01:03
000A00E8-0073-00D5-9900-3A0061000F0007/12/2024 01:03
00A10064-0076-0007-0B00-340087001E0007/12/2024 01:03
0060007D-00D4-00ED-A800-F800B600560007/12/2024 07:19
5B27047E-6E55-44C0-AA46-389CD250EAF407/12/2024 07:20
009000C5-0051-001E-3600-F6006700DE0007/12/2024 07:30
E8208E22-2396-4080-ACC4-11753B215CD907/12/2024 07:31
00B700A4-0098-00EB-F800-EF00E500A30007/12/2024 07:31
00A20066-004D-00D3-C100-5D00D100140007/12/2024 07:31
384AF161-C56A-462D-A1DF-2B723B02EBC707/12/2024 07:35
danextian's avatar
danextian
Super User
1 year ago

Hi simoncotterill 

 

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"

 

  • simoncotterill's avatar
    simoncotterill
    Regular Visitor
    1 year ago

    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?

    • danextian's avatar
      danextian
      Super User
      1 year ago

      Hi simoncotterill 

      , 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