Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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, @Anonymous. 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, @Anonymous. 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.

Anonymous
Not applicable

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

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 🙂

Anonymous
Not applicable

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

 

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors