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.

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