Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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
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
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
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"
Thank you So much. This works. I appreciate your time and help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |