Forum Discussion
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 duration and then to total seconds(throws error). Can someone write a power query code to create a new column which has the total seconds( for eg., 24*3600+53) when power bi doesn't recognizes that I have more than 24 hours and takes only the remainder hours and minutes.
3 Replies
ppm1Solution Sage
You 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}/100Pat
IdkpowerbiActually 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