New Column to create time bands

[KG1]

Hi

I need to create a new column which puts a time into a time bracket

The time brackets are in hourly slots

00:00-01:00

01:00-02:00

02:00-03:00

03:00-04:00

04:00-05:00

05:00-06:00

My Sample Data Set looks like this

Time

00:02:10

09:45:56

06:33:53

00:00:20

00:01:42

The expected result with the new column would look like this:

Time		Time Bracket
00:02:10		00:00-01:00
09:45:56		09:00-10:00
06:33:53		06:00-07:00
00:00:20		00:00-01:00
00:01:42		00:00-01:00

Any help would be greatly appreciated

Thank you in advance

[Samarth_18 · Accepted solution]

Hi @KG1 

You can create a column with below code:-

Time_Bracket =
VAR start_hour =
    FORMAT ( Time_Bucket[Time], "hh" )
VAR end_hour =
    IF ( LEN ( start_hour + 1 ) = 1, "0" & start_hour + 1, start_hour + 1 )
RETURN
    start_hour & ":00 - " & end_hour & ":00"

Thanks

Samarth

[Samarth_18 · Accepted solution]

Hi @KG1 

You can create a column with below code:-

Time_Bracket =
VAR start_hour =
    FORMAT ( Time_Bucket[Time], "hh" )
VAR end_hour =
    IF ( LEN ( start_hour + 1 ) = 1, "0" & start_hour + 1, start_hour + 1 )
RETURN
    start_hour & ":00 - " & end_hour & ":00"

Thanks

Samarth

[KG1]

Thank you for replying but I get the following error

Cannot convert value '' of type Text to type Number.

[Samarth_18]

Could you please let me know the data type your time column and also if you have any blank values in time column?

[KG1 · Accepted solution]

There were some blank rows at the end of the dataset - I have removed these and it's worked perfectly

Thank you very much for your help

[Samarth_18]

If you want to keep those blanks rows then you can refere below code:-

Time_Bracket =
VAR start_hour =
    FORMAT ( Time_Bucket[Time], "hh" )
VAR end_hour =
    IF ( LEN ( start_hour + 1 ) = 1, "0" & start_hour + 1, start_hour + 1 )
VAR result = start_hour & ":00 - " & end_hour & ":00"
RETURN
    IF ( NOT ( ISBLANK ( Time_Bucket[Time] ) ), result, BLANK () )