Convert Data Type to time or Duration

[dananjayaprasad]

Hello , 

I have a time period that I need to covernt in to a time period on PowerBI, but it gives me a error, I tried sever ways did not work. I am kinda stumped. I would really appreciate if anybody tell me what wrong.  

Data

Location	Time Period	Traffic	% Traffic
100 - Park City	7:00 AM - 7:59AM	3	0.024
100 - Park City	8:00 AM - 8:59AM	3	0.024
100 - Park City	9:00 AM - 9:59AM	2	0.016
100 - Park City	10:00 AM - 10:59AM	8	0.063
100 - Park City	11:00 AM - 11:59AM	10	0.079
100 - Park City	12:00 PM - 12:59PM	12	0.094
100 - Park City	1:00 PM - 1:59PM	14	0.11
100 - Park City	2:00 PM - 2:59PM	12	0.094

When I convert it, gives me below error

Thank you So mcuh

DHana 

[Vvelarde]

@dananjayaprasad

HI, Dhana:

Please follow the steps :

Regards

Victor

Lima  - Peru

[dananjayaprasad]

Hey Victor, 

Thank you so much for clear instructions. I learn new thing too, but I think I counfused you a bit. please see below image. My time period does not recognize as time. 

You can see this picture, my time period take it as a General Number. Its should be sort as 7. AM to PM, but it is not. I try to convert the colounm as time. Doest not work. 

I would really appreciate your time. 

Thank you 

Dhana

[Vvelarde]

@dananjayaprasad

You need to specify the sort of the interval times.

You can find a sample in this thread:

https://community.powerbi.com/t5/Desktop/Sorting-by-Month-as-Text-Jan-Feb-Mar-etc/td-p/9237

Regards

Victor

Lima - Peru

[dananjayaprasad]

Hey Vector, 

Now I got a new problem. Do you know by any chance. how to concatenate two time? I used two column, it gives me a error. I want something like this 7.00.00 AM - 7.59.00 AM. ( Sorry, I could not figure it out the time intervel) 

Thank you so much

Dhana 

[MarcelBeug · Accepted solution]

DHana, please understand that a time value is 1 timestamp; if converted to a number then it will be between 0 and 1.

A Time Period like yours is just text.

Having said that, in the Query Editor: Add Column - Extract - Text Before Delimiter: delimiter <space>, advanced, skip 1.

Rename the column to Start Time and adjust the data type to Time.

Close & Load. In the Data view, tab Modeling, select Time Period and sort by Column Start Time.

Generated query code (the first 2 steps just create the table):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc+9CsMwDATgVzGe06JzfmxlC5kD3kOGjKVb6dK3r2on9hJBB4E4+DhpXS2IzM3E/fU08+P9sY31o0TTIqkfe54WiVoZupPr7NZckVBI+JdwIXwSlwkGhYCKkfVAIaOh1RAqwolAWXnWlPupmJQTFZM6DmTtJ1RUTJcMoJDac1mzfQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Location = _t, #"Time Period" = _t, Traffic = _t, #"% Traffic" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Time Period", type text}, {"Traffic", Int64.Type}, {"% Traffic", type number}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Start Time", each Text.BeforeDelimiter([Time Period], " ", 1), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Before Delimiter",{{"Start Time", type time}})
in
    #"Changed Type1"

[dananjayaprasad]

Thank you So much. This works. I appreciate your time and help.