cancel
Showing results for
Did you mean: 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) 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?

1 ACCEPTED SOLUTION  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.

6 REPLIES 6  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. Frequent Visitor

Thanks, I'll give that a go. Thought the solution would be pretty simple 😅  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  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,   Community Champion

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

Thank you for the kudos 🙂

Proud to be a Super User! Frequent Visitor

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

Thanks!   