Forum Discussion
Idkpowerbi
Helper I
Helper IHelp - Calculating total seconds of a date/time column
My input data looks like the right column and power bi recognizes in the following format(text). There are some duration values greater than 24 hours. I am unable to change data type to dur...
ppm1
Solution Sage
Solution SageYou can add a custom column with this expression to get total seconds.
let
parsedlist = List.Transform(Text.Split([Duration], ":"), each Number.From(_) )
in
parsedlist{0}*3600 + parsedlist{1} + parsedlist{2}/100
Pat
IdkpowerbiHelper I
Helper IActually the problem is that the data is imported from an excel where it uses the format(1/1/1900) to store time more than 24 hours and power query recognizes this as shown above. Can you calculate total seconds from this type.
- ppm1
That should be easier. Just convert it to a DateTime type first, then convert it to a decimal and, when prompted, choose "Add as a new step". This will give your duration in days, and you can do a multiply transform to convert it to seconds. Or you can leave it as decimal in days, and just multiply to get seconds in your measures.
Pat