Forum Discussion
Help! Converting decimal to date/time format string doesnt always work
miguelsus2000 I would do this in Power Query if possible, looks like your Day is wrong, which is causing the problems with the other calculations. I've followed the same logic as you though. I'm curious why that Day value is wrong though - can you provide a sample file or more context?
Paste this code into a new blank Query in Power Query editor and see if it makes sense/does the trick:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcxBCgAgCETRu7iOwTStziLe/xqBuXswn4mgCVbKESTgW2AYFwx+CgqRnrpRbG/o/vGE2z8S3EWZDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccTimeInStep = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AccTimeInStep", type number}}),
#"Inserted Days" = Table.AddColumn(#"Changed Type", "Days", each Number.RoundDown([AccTimeInStep]), Int64.Type),
#"Inserted Remaining Hours" = Table.AddColumn(#"Inserted Days", "Remaining Hours", each ([AccTimeInStep] - [Days])*24, type number),
#"Inserted Hours" = Table.AddColumn(#"Inserted Remaining Hours", "Hours", each Number.RoundDown([Remaining Hours]), Int64.Type),
#"Inserted Remaining Minutes" = Table.AddColumn(#"Inserted Hours", "Remaining Minutes", each ([Remaining Hours] - [Hours])*60, type number),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Remaining Minutes", "Minutes", each Number.RoundDown([Remaining Minutes]), Int64.Type),
#"Inserted Remaining Seconds" = Table.AddColumn(#"Inserted Minutes", "Remaining Seconds", each ([Remaining Minutes] - [Minutes])*60, type number),
#"Inserted Seconds" = Table.AddColumn(#"Inserted Remaining Seconds", "Seconds", each Number.RoundDown([Remaining Seconds]), Int64.Type),
#"Inserted TimeKey" = Table.AddColumn(#"Inserted Seconds", "TimeKey", each ([Days] * 1000000) + ([Hours] * 10000) + ([Minutes] * 100) + [Seconds], type number),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Inserted TimeKey", {{"TimeKey", type text}}, "en-NZ"), "TimeKey", Splitter.SplitTextByPositions({0,2,4,6}, true), {"D", "HH", "MM", "SS"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Position","","0",Replacer.ReplaceValue,{"D"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"D", "HH", "MM", "SS"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"D:HH:MM:SS")
in
#"Merged Columns"
Thank you Greg. I might have tried something similar... it looks better sitll have a couple discrepancies. I would have thought TRUNC is better as INT rounds to the closest integer; TRUNC strips the decimal. See below. This should be very straightforward, but wonder if there is anythign in my config not allowing the truncating of some of the values.
miguelsus2000 I don't see the same discrepencies when using my formulas. Check out Table 11 in attached PBIX below signature. TRUNC and INT are only different when using negative numbers.
Thank you Greg... gonna try out and get back.
miguelsus2000 Not sure, but this might make things clearer:
Conversion = VAR __Value = [Column1] VAR __Days = INT(__Value) VAR __Hours = INT ( (__Value - __Days) * 24 ) VAR __Minutes = INT ( (__Value - __Days - __Hours / 24) * 24*60 ) VAR __Seconds = INT ( (__Value - __Days - __Hours / 24 - __Minutes/24/60) * 24*60*60 ) RETURN __Days&":"&__Hours&":"&__Minutes&":"&__Seconds