Forum Discussion
Helper IIRecognise Column as a Time?
Hi all,
First time using Power BI, so apologies if it sounds confusing/doesn't make sense or isn't possible.
Basically, I've got a dataset that records values every 30 mins and stores them under columns "half hour 1" to "half hour 48". What I'm trying to do is to use these columns to be able to drill down by every hour and half hour. Is there a way to have it recognise "half hour 1" as "00:30", "half hour 2" as "01:00" etc. ?
Or is there an easier way to do this to drill down by every hour/half hour? There's no timestamps in the data, only dates.
All help appreciated, thanks.
15 Replies
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
- Greg_Deckler
I think this will help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
