Calculate total number of minutes in PowerBI

Question

I am a fairly beginner user of PowerBI and still learning the ropes about DAX / PowerQuery. I have a column called "Time Usage" that has the time in text format: "1d 10h 25min", "2h 1d 30min", "5m 2h 3d" - a bit inconsistent.

What is the best way to calculate the total number of minutes? One solution I can think of is splitting the column in PowerQuery by a space delimiter and using nested IF/SWITCH statements in DAX, however this seems inefficient.

Thanks for you help!

Answer 1

Add a new Custom Column, and enter this formula:

let
  txt = " " & [Time Usage] & " ",
  dd = Text.Select(List.Last(Text.Split(Text.BetweenDelimiters(txt, " ", "d"), " ")), {"0".."9"}),
  hh = Text.Select(List.Last(Text.Split(Text.BetweenDelimiters(txt, " ", "h"), " ")), {"0".."9"}),
  mm = Text.Select(List.Last(Text.Split(Text.BetweenDelimiters(txt, " ", "m"), " ")), {"0".."9"}),
  ss = Text.Select(List.Last(Text.Split(Text.BetweenDelimiters(txt, " ", "s"), " ")), {"0".."9"}),
  dur = #duration(Number.From(dd)??0, Number.From(hh)??0, Number.From(mm)??0, Number.From(ss)??0),
  tm = Duration.TotalMinutes(dur)
in
  tm

This will support days, hours, minutes, and seconds - provided in full or in parts, and in any order.