Forum Discussion
Duration format
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.
Just right click the table in Query Editor, click Advanced Editor, and copy paste the contents here.
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"
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.
Awesome
Thanks Greg_Deckler