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
  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    = Table.SelectRows(#"Changed Type with Locale", each Time.From([EventTime]) >= #time(17,0,0) or Time.From([EventTime]) <= #time(5,30,0))

  • 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

         

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello simoncotterill 

     

    just my curiosity, does simple row filtering not work for your case?

    after you split column between date and time as you mentioned in your first post, then just do time filter the row with between condition and using OR operator.

     

    Thank you.