Forum Discussion
power query time format change
Hi , could anyone tell me what is the power query formula to change the hour into hh:mm format
4 Replies
Learner_SG I believe you would just use the divide method in Power Query and then change the type to Duration.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGK1SFLJBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [One = _t, Two = _t, Three = _t, Four = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"One", Int64.Type}, {"Two", Int64.Type}, {"Three", Int64.Type}, {"Four", Int64.Type}}), #"Divided Column" = Table.TransformColumns(#"Changed Type", {{"One", each _ / 24, type number}}), #"Changed Type1" = Table.TransformColumnTypes(#"Divided Column",{{"One", type duration}}) in #"Changed Type1"Greg_Deckler , thanks for the reply. I tried with your formula but it just displays one time only and it does not give different time values as per the time table.
My intention is that based on my hour value table which is in 24 hr format , I need to display it in the graph which will show as below.
When I used the time column which is hh:00 format which was created in dax it gives me the date also inside(31/12/1899)
even though I tried to change the column to time format. So I wanted to create the table in power query and try.
Learner_SG There is no true Duration data type in Power BI even though it exists in Power Query. It is stored as a decimal number with the hours, minutes, seconds as /24, /24/60 and /24/60/60. So, you will need to use a custom format string along the lines of Chelsie Eiden's Duration. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
