Forum Discussion
Add timecolumn to another timecolumn
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?
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"Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
5 Replies
can you change the second column to data type Duration and then add the 2 columns?
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"Hi v-frfei-msft and Stachu ,
That doesn't work. It gives this error:
DataFormat.Error: Can't convert to duration.
Details:
00:00:00So 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"Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Yes, that's the trick. Great!
