How to do IF on power query editor between 2 different times, both HH:MM?

[alexw94]

Hello, I am looking to create a new row in power query editor data that will give my numeric figures based on if the criteria is set between 2 times going by HH:MM format. For instance I am wanting get a 1 added into a new column for when the criteria is between 00:00 - 00:15. 

I have tried to search on here and the web and have found nothing for what I am trying to create. 

I have tried out the following:

Custom Column created  = IF [ColumnA] => 00:00 AND [ColumnA] <= 00:15 then 1 else 0.

I am getting errors and dont know where to go. Column A with the time values in all are set as "Time" values at the header of the column. Any help would be gratefully appreciated?

Thanks,

Alex

[v-yueyunzh-msft · Accepted solution]

Hi, @alexw94 

According to your description, you want to add a column (1,0) judged by the criteria is between 00:00 - 00:15. Right?

Here are the steps you can refer to :
(1)This is my test data:

(2)And we also change the date type to "Time":

(3)Then we can add a custom column and enter this:

if [Time] >= Time.From("00:00")  and [Time] <= Time.From("00:15") then 1 else 0

(4)Then we can meet your need:

The whole M code is like this , you can put in the "Advanced Editor" to refer to :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjRVitUBswwMYCxDI7iYBVzMDMYyNoawDK0MTSAsCysjiA5DYzArFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Time] >= Time.From("00:00")  and [Time] <= Time.From("00:15") then 1 else 0)
in
    #"Added Custom"

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly