Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thank you in advance
Solved! Go to Solution.
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.
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.
Thanks, I'll give that a go. Thought the solution would be pretty simple 😅
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"
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! |
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,
can you use M (Power Query) for this, or does it have to be a DAX solution?
Either is fine. This data set will be updated daily so one might be more applicable than the other.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |