Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello all, I have a Duration column on a report that I need to transform into total minutes.
The current outputs are a variation of the follow three formats:
15 s
11 m 57 s
2 h 15 m 8 s
I really only need total minutes, but would be fine with a format that can average the total time in h:m:s. I'm still learning Power BI and would appreciate some guidance.
Solved! Go to Solution.
Hi @SenoirB
I modified @AntrikshSharma 's solution as below. You can use this code to create a custom column.
let result =
Text.Replace (
Text.Replace (
Text.Replace (
Text.Replace ( [Time], " ", "" ),
"h", "*60+"
),
"m", "*1+"
),
"s", "/60"
)
in
if Text.EndsWith(result, "+")
then Expression.Evaluate(Text.Range(result, 0, Text.Length(result)-1))
else Expression.Evaluate(result)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Here is another way to solve this:
Expression.Evaluate (
Text.Replace (
Text.Replace (
Text.Replace (
Text.Replace ( [Time], " ", "" ),
"h", "*60+"
),
"m", "*1+"
),
"s", "/60"
)
)
This one was really close but I get an error if there isn't a "s" on the value. Very few of the entries have a value that looks like this: 14 m
I didn't know these were on the there without seeing the errors from the Custom Column. I tried to come up with a solution by Splitting the column and replacing values and couldn't.
Hi @SenoirB
I modified @AntrikshSharma 's solution as below. You can use this code to create a custom column.
let result =
Text.Replace (
Text.Replace (
Text.Replace (
Text.Replace ( [Time], " ", "" ),
"h", "*60+"
),
"m", "*1+"
),
"s", "/60"
)
in
if Text.EndsWith(result, "+")
then Expression.Evaluate(Text.Range(result, 0, Text.Length(result)-1))
else Expression.Evaluate(result)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
This worked perfectly! Thank you!
To transform the Duration column into total minutes in Power BI, you can follow these steps:
Hello @Adamboer thank you for the reply. I tried adding your formula to a custom column and I get errors for results. The row is called Duration so I'm not sure? Does the formula account for the letter "h", "m", "s" that are in the values?
Thank you for the response. I may have left out some details. I copy and paste the data into Sharepoint, so the report's data source is from Sharepoint. I was entering your code in a Custom Column formula, I don't think that is correct either because it was creating a Table in the Added Column for each entry. Sorry I am really new to Power Queries and Power BI. But your result in Total Minutes is exactly what I am after.
@SenoirB My code is query so it will return a table, you need to use the AddedCustom step and create a new step in your code. Otherwise just paste the whole code in a blank query and you can see how it works.
@SenoirB Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRVKFaK1QEyDHMVTM0hbKMMQ9NcCyA7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
ChangedType = Table.TransformColumnTypes ( Source, { { "Time", type text } } ),
AddedCustom =
Table.AddColumn (
ChangedType,
"Total Minutes",
each
let
RemoveSpace = Text.Replace ( [Time], " ", "" ),
Split = Splitter.SplitTextByCharacterTransition ( { "a" .. "z" }, { "0" .. "9" } ) ( RemoveSpace ),
Transform =
List.Transform (
Split,
each
let
Number = Number.From ( Text.Select ( _, { "0" .. "9" } ) ),
Correction =
if Text.Contains ( _, "h" ) then Number * 60
else if Text.Contains ( _, "m" ) then Number * 1
else if Text.Contains ( _, "s" ) then Number / 60
else null
in
Correction
)
in
Number.Round ( List.Sum ( Transform ), 2 ),
type number
)
in
AddedCustom
User | Count |
---|---|
25 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
44 | |
27 | |
21 | |
16 | |
12 |