cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KG1
Helper II
Helper II

New Column to create time bands

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

2 ACCEPTED SOLUTIONS
Samarth_18
Solution Sage
Solution Sage

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"

Samarth_18_0-1627894341064.png

Thanks

Samarth

View solution in original post

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

View solution in original post

5 REPLIES 5
Samarth_18
Solution Sage
Solution Sage

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"

Samarth_18_0-1627894341064.png

Thanks

Samarth

View solution in original post

Thank you for replying but I get the following error

 

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

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

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

View solution in original post

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

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors