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!
The data source that I am using imports times as text instead of the number of minutes. Is there anyway I can use any of the functions listed above to show the # of minutes?
Thank you!!!
Solved! Go to Solution.
Hi @Anonymous
PattemManohar's suggestion is very helpful, you may try it in Edit Queries.
Of course, you could also use the function you mentioned to achieve your requirement.
To let you understand more, i create calculated columns step by step.
Column1 = FIND("min",[time],1) Column 2 = LEFT([time],2) Column 3 = RIGHT([time],6) Column 4 = LEFT([Column 3],2) Column5 = IF([Column1]>4,[Column 2]*60+[Column 4],[Column 2])
Note you need transform [Column1],[Column2],[Column4] to type whole number
Or you could nest columns
co2 = LEFT([time],2) co4 = var co3 = RIGHT([time],6) return LEFT(co3,2) time tranform = var co1 = FIND("min",[time],1) return IF(co1>4,[co2]*60+[co4],[co2])
Best Regards
Maggie
Hi @Anonymous
PattemManohar's suggestion is very helpful, you may try it in Edit Queries.
Of course, you could also use the function you mentioned to achieve your requirement.
To let you understand more, i create calculated columns step by step.
Column1 = FIND("min",[time],1) Column 2 = LEFT([time],2) Column 3 = RIGHT([time],6) Column 4 = LEFT([Column 3],2) Column5 = IF([Column1]>4,[Column 2]*60+[Column 4],[Column 2])
Note you need transform [Column1],[Column2],[Column4] to type whole number
Or you could nest columns
co2 = LEFT([time],2) co4 = var co3 = RIGHT([time],6) return LEFT(co3,2) time tranform = var co1 = FIND("min",[time],1) return IF(co1>4,[co2]*60+[co4],[co2])
Best Regards
Maggie
@Anonymous Please use below in "Advanced Editor" in "Power Query Editor"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHIzcxTitWJVjKDs4yM4UwTczjTUCGjSAEhY2yEUA+SgXJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}}), #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Time]){1}?, type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Custom"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Time.1", "Time.2", "Time.3", "Time.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time.1", Int64.Type}, {"Time.2", type text}, {"Time.3", Int64.Type}, {"Time.4", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Time3New", each if [Time.3] = null then [Time.1] else [Time.3]), #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "HrToMin", each if [Time.2]="hr" then [Time.1]*60 else 0), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Time.1", "Time.2", "Time.3", "Time.4"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Time3New", "Min"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "TotalMin", each [Min]+[HrToMin]) in #"Added Custom1"
Proud to be a PBI Community Champion
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |