Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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"
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 .)
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"
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |