Forum Discussion

dananjayaprasad's avatar
dananjayaprasad
Helper I
8 years ago
Solved

Convert Data Type to time or Duration

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

LocationTime PeriodTraffic% Traffic
100 - Park City7:00 AM - 7:59AM30.024
100 - Park City8:00 AM - 8:59AM30.024
100 - Park City9:00 AM - 9:59AM20.016
100 - Park City10:00 AM - 10:59AM80.063
100 - Park City11:00 AM - 11:59AM100.079
100 - Park City12:00 PM - 12:59PM120.094
100 - Park City1:00 PM - 1:59PM140.11
100 - Park City2:00 PM - 2:59PM120.094

 

When I convert it, gives me below error

 

Thank you So mcuh

DHana 

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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"

     

     

6 Replies