Forum Discussion
Helper IIRecognise Column as a Time?
CalvinLDate Half Hour 1 Half Hour 2 Half Hour 3
Half Hour 4 Half Hour 5 Half Hour 6 Half Hour 7 Half Hour 8 Half Hour 9 01/01/2019 87 80 67 81 82 84 77 78 65 20/04/2019 89 90 92 67 78 85 88 86 71 10/05/2019 90 88 76 78 75 86 82 81 82 15/03/2019 88 75 90 91 77 72 81 85 88 07/02/2019 90 92 78 65 84 77 91 84 72 Sample data above^
camargos88Community Champion
Hi CalvinL ,
Try this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDRDcAgCER38buJQFV0FuP+a5TDiGmihwq8nMyZiLMtIR7pSV0hZNL8xBCBFBPFm3ZkqwmSO65nJqFMJTiQAc6QA/PGjobup4Y3MFBRPILDxqmH4wgv13YQWk/3dhYe39jOqZne8BON2xTHby4inJXwBA5pJvn58S/dMdzROHZfJWaDuNYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Half Hour 1" = _t, #"Half Hour 2" = _t, #"Half Hour 3" = _t, #"Half Hour 4" = _t, #"Half Hour 5" = _t, #"Half Hour 6" = _t, #"Half Hour 7" = _t, #"Half Hour 8" = _t, #"Half Hour 9" = _t, #"Half Hour 10" = _t, #"Half Hour 11" = _t, #"Half Hour 48" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Half Hour 1", Int64.Type}, {"Half Hour 2", Int64.Type}, {"Half Hour 3", Int64.Type}, {"Half Hour 4", Int64.Type}, {"Half Hour 5", Int64.Type}, {"Half Hour 6", Int64.Type}, {"Half Hour 7", Int64.Type}, {"Half Hour 8", Int64.Type}, {"Half Hour 9", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Duration.ToText(#duration(0, 0, 30, 0) * Number.FromText(Text.Split([Attribute], " "){2}))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}, {"Value", Int64.Type}, {"Date", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Date] + [Custom]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}})
in
#"Changed Type2"Did I answer your question? Mark my post as a solution!
Ricardo- CalvinL
Hi camargos88 ,
That didn't seem to work for me however, I think we're on the right track. One of my friends recommended trying to transform the column and assign time to the columns. Any idea how I can go about doing that?