Change Duration Column from Text Type to Number/Time Data Type! HELP!

Hi! I created some sample data:

In Power Query, I added a custom column to create a total seconds column. In PQ, go to Add Column ribbon, then Add Custom. Name your column TotalSeconds, then paste the below in the formula area:

let
DurationText = [Duration],
Parts = Text.Split(DurationText, ":"),
Hours = Number.FromText(Parts{0}),
Minutes = Number.FromText(Parts{1}),
Seconds = Number.FromText(Parts{2}),
TotalSeconds = (Hours * 3600) + (Minutes * 60) + Seconds
in
TotalSeconds

You will now have this:

Next, set TotalSeconds to whole number. Close and apply so your data loads.

Now, make a DAX measure:

TotalDuration =

VAR TotalSeconds = SUM('Table (4)'[TotalSeconds])

VAR Days = INT(TotalSeconds / 86400)

VAR RemainderAfterDays = MOD(TotalSeconds, 86400)

VAR Hours = INT(RemainderAfterDays / 3600)

VAR RemainderAfterHours = MOD(RemainderAfterDays, 3600)

VAR Minutes = INT(RemainderAfterHours / 60)

VAR Seconds = MOD(RemainderAfterHours, 60)

RETURN

    FORMAT(Days, "00") & ":" &

    FORMAT(Hours, "00") & ":" &

    FORMAT(Minutes, "00") & ":" &

    FORMAT(Seconds, "00")

 

And, if you add whatever dimension you want and your measure in a visual, you can see it is all adding up nicely 🙂