Calculate new value/column if time falls within a range

[hankmobley]

Hi all,

I have a timestamp column called "Transaction Time" that I get via Direct Query in this format: "Sat Feb 26 08:36:53.656 AEDT 2022".  I would like to create a new column with the following conditions:

If transaction time falls within 9am-6:59pm = "Day"

If transaction time falls within 7pm-8:59am = "Night"

I'm fairly new to PowerBI so I'm not sure where to begin, any help would be appreciated.

Thanks in advance.

[JohnSnow]

TimeCategory = VAR TimeOfDay = TIME(HOUR([YourDateTimeField]), MINUTE([YourDateTimeField]), SECOND([YourDateTimeField])) RETURN IF( TimeOfDay < TIME(7, 0, 0), "Early morning", IF( TimeOfDay >= TIME(17, 0, 0), "Evening", "Working hours" ) )

[v-angzheng-msft]

Hi, @hankmobley 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

Best Regards,
Community Support Team _ Zeon Zheng

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

[hankmobley]

Hi @v-angzheng-msft 

Unfortunately I have still not found a solution.  Hoping someone else could help me with this issue.

Thanks.

[davehus]

Hi @hankmobley, the formula should read Text.Middle([execution_time],11,8). The "=" sign is there by default.

Delete the Parse Time and other "=" sign

[hankmobley]

Hi @davehus 

Thanks, I have made the adjustment.

I am now getting an error in the new column along with a Direct Query error:

Sorry but I also realised i provided the wrong date/time format.  The correct format is the one below.  I'm assuming this is the reason for the error?

Really appreciate your help.

[davehus]

Hi @hankmobley , I see. I thought from your original post, you would need to parse out the time from the cell ending in AEDT 2022. In your example above, you should be able to convert the column to a time and then just apply the case statement in a custom column.

I don't think something as simple as this would force an import but if it does, we may have to try the DAX route.

So convert exection_time to time and then add a custom column as below

= if [execution_time] > Time.FromText("09:00:00") and [execution_time] < Time.From("18:59:00") then "Day" else "Night"

[hankmobley]

Hi @davehus 

I went into Query Editor and when I converted the execution_time column to time only, it brought up the same import message:

Does that mean I can't continue with this option and should instead try DAX?

[davehus]

Seems like that is the case. Try this instead.

Create a new column in the desktop

Time = FORMAT([Execution_Time]),"hh:mm:ss")

Set the column type to time

New column

Day/Night = IF(AND(TIME(HOUR('Table'[Time]),MINUTE('Table'[Time]),SECOND('Table'[Time]))>TIME(09,00,00),TIME(HOUR('Table'[Time]),MINUTE('Table'[Time]),SECOND('Table'[Time]))<TIME(18,59,00)),"Day","Night")

[hankmobley]

Hi @davehus 

It looks like there is something about the formula it doesn't like:

I also tried this variation by taking out the ")" before the comma and it game me the below error message:

[davehus]

Hi Hank, Is that a measure you're creating? As it should be a column. I notice there is no table prefix on before execution time. Both calculations are columns. 'Table'[execution_time]

D

[hankmobley]

Sorry, yes I had left out the table prefix, but have tried it again and still the same issue:

I wasn't sure if it needed the ' ' before and after the Table name, so I added those also:

Both examples above are with and without the ")" before the comma.

[davehus]

Hi @hankmobley , it looks like we're snookered this way too. Format isn't permitted on direct query mode either 😕 I don't use DQ that much, so didn't read up the documentation on restrictions. Is there anyway that you can format the time at the source before it's imported? Where is your data being pulled from? Alternatively you'd have to import the data. 

[hankmobley]

Thanks @davehus 

I was also looking through some other posts for solutions and I came accross a Measure that is very similar to the New Column formula you suggested:

It is a measure though, instead of a new column.  Is this helpful in any way in order to find a solution?  Are you able to use DAX with this NewTime measure?

[davehus]

Hi @hankmobley , Yes it should work. Once you don't need the Day/Night column for a slicer or anything then the measures should work for you. 🙂 

[hankmobley]

Unfortunately yes, I will need to use day/night in a slicer.  But thank you so much for all your efforts in trying to help me.

If anyone else has any other ideas on how this could work it will be greatly appreciated.

Thanks.

[hankmobley]

Hi @davehus and all,

It turns out I was able to change the 'execution_time' column by going to the Model view, clicking on the column name and then under Properties, changing the format to Time:

I then went back into the report view, created a new column and used the DAX formula you suggested above, but I changed the 'Table'[Time] to my table and column name:

Day/Night = IF(AND(TIME(HOUR('Table'[Time]),MINUTE('Table'[Time]),SECOND('Table'[Time]))>TIME(09,00,00),TIME(HOUR('Table'[Time]),MINUTE('Table'[Time]),SECOND('Table'[Time]))<TIME(18,59,00)),"Day","Night")

The formula works, as in it doesn't give me any errors, but when I try and test it on a table next to the original 'execution-time' column, it gives me this error:

I feel like I'm so close to a solution.  Can you suggest a formula that does not use the TIME function?

Thanks.

[davehus]

Hi @hankmobley, my only other thought is to parse out the hour of the time and convert it to a number. Then do a calculation where if its between 9 and 19 then day else night. Try that and if no success, I'll try and have a look later. 👍

[hankmobley]

Thanks @davehus , that would be great.  I tried to parse out the hour but didn't have any luck, hopefully you can have a look when you get a chance.  Thanks again.

[davehus]

Hi @hankmobley ,

In Power Query in the table with the Transaction time, create the following column to parse the time from your column.

ParseTime = Text.Middle([Date],11,8)

Change Data Type to Time and rename to Time

Add a custom column and input the calculation below

= if [Time] > Time.FromText("09:00:00") and [Time] < Time.From("18:59:00") then "Day" else "Night"

If this meets your needs, please accept my answer.

[hankmobley]

Hi @davehus 

Thanks for your reply.  Can you please clarify how I create the new ParseTime column?

When I'm in Power Query, do I select the column with the current long format transaction time and duplicate it?  Then once I duplicate it, do I enter: ParseTime = Text.Middle([Date],11,8) in the formula bar?

Thanks.