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
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