Forum Discussion
Solved
Duration format
Hello Community This is my first time I post here :) I have a problem, I have a column with duration formatting as follows 9h 53m 38s and I want to convert it to HH:MM (09:53) How can ...
Abdel_Spateof, in response to your message, perhaps create DAX columns like this:
Hours = VAR __h = FIND("h",[Time],,BLANK()) VAR __final = IF(NOT(ISBLANK(__h)),__h-1,BLANK()) RETURN IF(NOT(ISBLANK(__final)),LEFT([Time],__final),BLANK()) Minutes = VAR __h = FIND("h",[Time],,BLANK()) VAR __finalh = IF(NOT(ISBLANK(__h)),__h-1,BLANK()) VAR __m = FIND("m",[Time],,BLANK()) VAR __finalm = IF(NOT(ISBLANK(__m)),__m-1,BLANK()) RETURN IF(ISBLANK(__finalm),BLANK(),IF(ISBLANK(__h),LEFT([Time],__finalm),MID([Time],__finalh+3,__finalm-__finalh-2))) Seconds = VAR __m = FIND("m",[Time],,BLANK()) VAR __finalm = IF(NOT(ISBLANK(__m)),__m-1,BLANK()) VAR __s = FIND("s",[Time],,BLANK()) VAR __finals = IF(NOT(ISBLANK(__s)),__s-1,BLANK()) RETURN IF(ISBLANK(__finals),BLANK(),IF(ISBLANK(__m),LEFT([Time],__finals),MID([Time],__finalm+3,__finals-__finalm-2)))After that, you can just concatenate them together as needed.
Hello Akhil;
I really appreciate your help. Unfortunately, it gives the same results
Can you provide the M-code which you tried? From what I see, it should work.
Here is what I did;
In query editor; I created a new custom column and I copy pasted your codes
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WssxQMDXOVTC2KFaK1YlWMjTIUDAyyFUwMoLyDTMUDIFcCM8sA0xZgtVA1UNokB5TA4gqMB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column.1", "Column.2", "Column.3"}), #"Replaced Value3" = Table.ReplaceValue(#"Split Column by Delimiter",null,"0",Replacer.ReplaceValue,{"Column.2"}), #"Added Custom1" = Table.AddColumn(#"Replaced Value3", "Hours", each if Text.EndsWith([Column.1],"h") then Text.TrimEnd([Column.1],"h") else "0"), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Minutes", each if Text.EndsWith([Column.2],"m") then Text.TrimEnd([Column.2],"m") else if Text.EndsWith([Column.1],"m") then Text.TrimEnd([Column.1],"m") else "0"), #"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Hours", "Minutes"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Time"), #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Time", type time}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Time"}) in #"Removed Other Columns"This is the same code I added in my comment. I wanted to see your modified M code.
Hi Akhil
Im Still new to the PowerBI; can you please guide me how I can get the Modified M Code?