Forum Discussion
Duration wrongly converted to Date-Time type
Anonymous
how are you getting to these duration values? are you subtracting 2 columns_? what do they look like?
LivioLanzo they are inserted by hand. No subtraction. They look like the picture below, which is a part of the excel sheet:
in the Query Editor - if I use the format
hh:mm:ss.000
in Enter Data I can change the data type to duration and only AFTER that to number
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjC0MjTUMzQxUIrVgQkYmOpZWiALGBrrGZqhqDDRMzRAETDTMwEJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type duration}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type number}})
in
#"Changed Type1"but if I import the same from Excel I cannot convert to duration, only to number but it adds the 1 to the value for some reason
does it work the same for you?
PowerBI does not currently support milliseconds in date time datatype. I solved the problems like follow:
convert the excel doc to general, load it into PowerBI, and multiply the value by 86400 to get the total number of seconds.
you can convert them within excel to general format and then copy paste them into Power BI
thank you for your help. It worked :smileyvery-happy:
I converted the source column to general, imported it in the PBI model and then set the data type as Duration.
The only problem remained, is that it is corrected the PowerQuery mode yet in the PBI desktop data section, it is not showed correctly :smileyfrustrated:
