Duration format

[Abdel_Spateof]

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 I do that ??

Thanks

[AkhilAshok]

You could achieve this in Power Query Editor. Please find the M code steps for that below with sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WssxQMDXOVTC2KFaK1YlWMjTIUDAyyFUwMoLyDTMUDIFcIC8WAA==", 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"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","h","",Replacer.ReplaceText,{"Column.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","m","",Replacer.ReplaceText,{"Column.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Column.1", Int64.Type}, {"Column.2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Column.1", type text}, {"Column.2", type text}}, "en-GB"),{"Column.1", "Column.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type time}})
in
    #"Changed Type3"

[Abdel_Spateof]

Hello Akhil, 

thank you very much for your response; 

the Code is not giving the right time ( pls see the screenshot)

I hope there is a simple solution for this .)

[AkhilAshok]

How about this:

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"

[Abdel_Spateof]

Hello Akhil; 

I really appreciate your help. Unfortunately, it gives the same results

[AkhilAshok]

Can you provide the M-code which you tried? From what I see, it should work.

[Abdel_Spateof]

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"

[AkhilAshok]

This is the same code I added in my comment. I wanted to see your modified M code.

[Abdel_Spateof]

Hi Akhil 
Im Still new to the PowerBI; can you please guide me how I can get the Modified M Code?

[AkhilAshok]

Just right click the table in Query Editor, click Advanced Editor, and copy paste the contents here.

[Abdel_Spateof]

Here It is

let
Source = Web.Page(Web.Contents("i removed the website; I cant share it")),
Data0 = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee", type text}, {"Month", type date}, {"Date", type date}, {"Path", type text}, {"Task", type text}, {"Time", type text}, {"Internal_Cost/h", type text}, {"Amount", type text}, {"External_Cost/h", type text}, {"Amount_1", type text}, {"Total amount", type text}, {"Notes", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Time", "Time - Copy"),
#"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each 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"),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Time"}, {"Custom.Time"})
in
#"Expanded Custom"

[Greg_Deckler · Accepted solution]

@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. 

[Abdel_Spateof]

Awesome

Thanks @Greg_Deckler