Forum Discussion
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 with a filter but the following day when the data updated to different dates it wouldn't work so I tried splitting the column to seperate the date and time but can't find a way of filtering the time when it goes past midnight.
The data will refresh every day so I can't really use the date to filter.
Any help please?
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
10 Replies
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.
I want to filter to only show between 17:00 to 05:30 i.e. overnight.Your filter will need to include the date part. You need to make sure that the "from" date part is different from the "to" data part - by one.
I did that originally but every day I refresh with a new days data I have to change the filter as the dates have changed. I was wondering if there was a way of doing it so Idon't have to change the filter each day
yes, that should be possible to be done automatically.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
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.
ID EventTime 00EC0034-00FE-0024-3E00-CA007C00D900 06/12/2024 08:04 BE85F6BB-9D93-4B13-AD4D-7E731FFBDCF9 06/12/2024 08:13 0096002E-00EF-0094-5500-9800C5008400 06/12/2024 08:13 58030BE5-2E3C-44BB-B37E-19FDCB1A5575 06/12/2024 08:13 9E0DA4AF-DA1C-4D8A-9D24-97CC5DC7701B 06/12/2024 08:13 00F20012-00EC-003A-0B00-490047008300 06/12/2024 08:13 41C6E185-4CD0-41CE-A3A9-57FE246FAA06 06/12/2024 08:13 99618192-6C71-4591-BE7C-C9695E77274F 06/12/2024 08:13 009A001B-00CD-0046-CB00-5F00DB006B00 06/12/2024 11:46 8C3B99FB-718E-41C8-B836-CC3193B12DD7 06/12/2024 11:46 95DA44B4-00AC-4329-84AB-1B0C16F349A5 06/12/2024 11:46 004100D1-005F-006E-D800-A20043009200 06/12/2024 11:47 26F3436D-E7E6-4707-9A36-5DEF31FB3EE3 06/12/2024 11:47 00E50097-00DB-0045-3F00-49000F009E00 06/12/2024 11:47 3C8DC833-C7F6-4169-BD82-284BEFE94238 06/12/2024 11:47 00370044-0045-00BD-2200-26007300ED00 06/12/2024 11:48 00F5009A-0002-005A-9B00-4C003C001800 06/12/2024 11:49 86291C49-B827-478A-BB32-7635A76B6FEE 06/12/2024 11:49 99EDEBB8-A199-4A23-8CBA-C50C177F36E4 06/12/2024 11:49 006B0003-0083-00E6-8900-07008A005800 06/12/2024 11:50 0065006C-00B3-00CE-9700-1300FE008200 06/12/2024 17:21 00770061-00CB-00D9-9100-0A004600E400 06/12/2024 17:21 008D00B0-0035-00DB-7C00-080093006F00 06/12/2024 17:21 00B900DD-002B-00DC-C200-8900CB006900 06/12/2024 17:21 00FC0079-00FF-00A2-6E00-850071002900 06/12/2024 17:21 7173FF85-1069-4D52-972D-3E0EAD26547E 06/12/2024 17:21 00D50008-0072-0040-4100-040092006600 06/12/2024 17:21 00B2001E-006A-006D-4B00-9E000C001500 06/12/2024 17:21 005800E6-00C3-00A5-4100-A6000F006F00 06/12/2024 17:21 008E0081-004F-0023-E100-700093004000 06/12/2024 17:21 67ACC59D-6733-42C9-BD99-3BF55FCD1D8E 06/12/2024 17:21 005A0006-0090-00BA-2100-D40012002200 07/12/2024 01:03 00A40068-0062-00FE-3B00-7E00BB00F600 07/12/2024 01:03 BE5A54C4-B6A8-483C-87B6-42040A0A1D6A 07/12/2024 01:03 4037CB48-DB90-4DC6-AAA2-A80B485F3462 07/12/2024 01:03 D2216615-7DD7-40F7-9573-A661FCEEE391 07/12/2024 01:03 7B834407-B2DF-4EFC-8C3D-D284A462E0D1 07/12/2024 01:03 000A00E8-0073-00D5-9900-3A0061000F00 07/12/2024 01:03 00A10064-0076-0007-0B00-340087001E00 07/12/2024 01:03 0060007D-00D4-00ED-A800-F800B6005600 07/12/2024 07:19 5B27047E-6E55-44C0-AA46-389CD250EAF4 07/12/2024 07:20 009000C5-0051-001E-3600-F6006700DE00 07/12/2024 07:30 E8208E22-2396-4080-ACC4-11753B215CD9 07/12/2024 07:31 00B700A4-0098-00EB-F800-EF00E500A300 07/12/2024 07:31 00A20066-004D-00D3-C100-5D00D1001400 07/12/2024 07:31 384AF161-C56A-462D-A1DF-2B723B02EBC7 07/12/2024 07:35 = Table.SelectRows(#"Changed Type with Locale", each Time.From([EventTime]) >= #time(17,0,0) or Time.From([EventTime]) <= #time(5,30,0))
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)
inAny ideas how to fix this please?
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.
