cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Objekt
Frequent Visitor

Finding total hours from inconsistent data

Hi, 

 

I'm trying to convert the total time from some inconsistent data like below into pure numbers of separate columns, one for hours and one for mins to then work out total hours. (e.g. top one = 19.92 hours)

Objekt_0-1625921991567.png

Most data is in the form h:m:s which is fine and I can calculate this by transforming the data out into different columns via delimiter and multiplying it all up into hours. However, as you can see some data is in the form d:h:m:s which means I can't use the previous method as then it counts the days column as an hour and messes up the total time. 

So, is there a way to split time with days into it's own column without affecting the data that is the form h:m:s or is there a more efficient way of doing it all together?

 

Thank you in advance

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

It's very easy to deal with this, @Objekt. In PQ, add a new column that will 1) check the number of ":" in the item, 2) will add a dummy day portion to the item if the number is 2 and return the original if otherwise, 3) use the split function (now all entries will have all components) and carry on like before. Very easy.

View solution in original post

6 REPLIES 6
daxer-almighty
Solution Sage
Solution Sage

It's very easy to deal with this, @Objekt. In PQ, add a new column that will 1) check the number of ":" in the item, 2) will add a dummy day portion to the item if the number is 2 and return the original if otherwise, 3) use the split function (now all entries will have all components) and carry on like before. Very easy.

View solution in original post

Thanks, I'll give that a go. Thought the solution would be pretty simple 😅

CNENFRNL
Community Champion
Community Champion

A generic custom function to split a duration string,

 

fn = (timeStr as text) =>
let
    sp  = Splitter.SplitTextByCharacterTransition({"0".."9"}, each not List.Contains({"0".."9"}, _)),
    l   = List.Zip(List.Transform(Text.SplitAny(timeStr, ":-\/"), each sp(_)))
in
    Record.FromList(List.Transform(l{0}, Number.From), List.Transform(l{1}, Text.Upper))

 

 

 

let
    fn = (timeStr as text) =>
    let
        sp  = Splitter.SplitTextByCharacterTransition({"0".."9"}, each not List.Contains({"0".."9"}, _)),
        l   = List.Zip(List.Transform(Text.SplitAny(timeStr, ":-\/"), each sp(_)))
    in
        Record.FromList(List.Transform(l{0}, Number.From), List.Transform(l{1}, Text.Upper)),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjD10Dcy8dU1sChWitUB8g0MU6yMMmJiDExyY2KMjILBokYuusbGuboGplBFhhlWhha+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dur = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Hr", each let dur = fn([Dur]) in Duration.TotalHours(#duration(Record.FieldOrDefault(dur,"D",0),Record.FieldOrDefault(dur,"H",0),Record.FieldOrDefault(dur,"M",0),Record.FieldOrDefault(dur,"S",0))))
in
    #"Added Custom"

 

Screenshot 2021-07-11 120605.png

Objekt
Frequent Visitor

Hi @CNENFRNL 
Thanks for your reply, I've put the code above into power query but it doesn't seem to be working. I'm not too familiar with M code so do you know whats wrong? do I need to input my 'Dur' coloum somewhere? 

Thank you, 

 

Objekt_0-1635948370016.png

 

Stachu
Community Champion
Community Champion

can you use M (Power Query) for this, or does it have to be a DAX solution?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Objekt
Frequent Visitor

Either is fine. This data set will be updated daily so one might be more applicable than the other.

 

Thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.