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.
I have a pbix file with 2 timecolumns coming from a CSV; 1 is a fixed value for the whole CSV (let's say 15:00:00) and the second column is chanching for every row (let's say; 00:00:01, 00:00:01, 00:00:01, 00:00:02, 00:00:02, 00:00:03, 00:00:03, 00:00:03, 00:00:03, 00:00:03......). Now I want to create a combined column which gives these output: 15:00:01, 15:00:01, 15:00:01, 15:00:02, 15:00:02, 15:00:03, 15:00:03, 15:00:03, 15:00:03, 15:00:03.
Can somebody give me a hint about what to do?
I've been trying so many things:
- using duration function (but couldn't get it done with a variable amount of seconds
- converting time to number
- converting time to text/string and stripping it and calculating the amount of hours, minutes and seconds
- adding a date to the first time to make it possible to use and so on
Everything is hard or gives problems. But I'm pretty sure it must be possible and not really hard. Somebody has a tip for me?
Solved! Go to Solution.
time is basically a fraction between 0 and 1 (24h)
try changing the type while adding a new step:
1) first from time to decimal
2) from decimal to duration
similar to this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjAAIiUdJTANYsbqYBM3xCFuRKK4qVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type time}, {"Column2", type time}}), #"Changed Type to decimal" = Table.TransformColumnTypes(#"Changed Type",{{"Column2", type number}}), #"Changed Type to duration" = Table.TransformColumnTypes(#"Changed Type to decimal",{{"Column2", type duration}}) in #"Changed Type to duration"
Proud to be a Datanaut!
Hi @v-frfei-msft and @Stachu ,
That doesn't work. It gives this error:
DataFormat.Error: Can't convert to duration.
Details:
00:00:00
So the problem is (I think) that you have a different format. I do have 00:00:00 and you do have 0.00:00:00... But I can't change it right now I guess?
time is basically a fraction between 0 and 1 (24h)
try changing the type while adding a new step:
1) first from time to decimal
2) from decimal to duration
similar to this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjAAIiUdJTANYsbqYBM3xCFuRKK4qVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type time}, {"Column2", type time}}), #"Changed Type to decimal" = Table.TransformColumnTypes(#"Changed Type",{{"Column2", type number}}), #"Changed Type to duration" = Table.TransformColumnTypes(#"Changed Type to decimal",{{"Column2", type duration}}) in #"Changed Type to duration"
Proud to be a Datanaut!
Yes, that's the trick. Great!
Hi @MiKeZZa ,
As @Stachu said, you should change the data type to durition. Here I made a sample for your reference, please check the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjAAIiUdJTBtZWCoFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type duration}, {"Column1", type duration}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1]+[Column2]) in #"Added Custom"
can you change the second column to data type Duration and then add the 2 columns?
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.