Forum Discussion
Solved
Need a calculated column based on the existing column
Hi All,
Need some help in calucation of time in hours and data is coming in the below format(directly from Source ITSM Ticekting tool)
We need to convert each row into days or hours or minutes so that i can use this filed for the other calucations
| Resolution Time in Bhrs 1mos 3w 3d 4h 39m 52s 1mos 3w 3d 4h 39m 52s 1mos 3w 3d 4h 39m 52s 1mos 3w 3d 4h 39m 52s 1mos 1w 2d 4h 24m 5s 1mos 1w 2d 4h 24m 5s 1mos 1w 2d 4h 24m 5s 1mos 1w 2d 4h 24m 5s 9h 42m 45s 9h 42m 45s 9h 42m 45s 9h 42m 45s 9h 34m 38s 9h 34m 38s 9h 34m 38s 9h 34m 38s 9h 22m 24s 9h 22m 24s 9h 22m 24s 9h 22m 24s 8m 6s 8m 6s 8m 6s 8m 6s 8m 0s 8m 0s 8m 0s 8m 0s 8h 40m 11s 8h 40m 11s 8h 40m 11s 8h 40m 11s 7m 50s 7m 50s 7m 50s 7m 50s 7m 39s 7m 39s 7m 39s 7m 39s 7m 39s 7m 39s 7m 39s 7m 39s 7m 23s 7m 23s 7m 23s 7m 23s 7h 8m 9s 7h 8m 9s 7h 8m 9s 7h 8m 9s 7h 8m 1s 7h 8m 1s 7h 8m 1s 7h 8m 1s 7h 6m 42s 7h 6m 42s 7h 6m 42s 7h 6m 42s 7h 4m 37s 7h 4m 37s 7h 4m 37s 7h 4m 37s 7h 35m 33s 7h 35m 33s 7h 35m 33s 7h 35m 33s 7h 27m 53s 7h 27m 53s 7h 27m 53s 7h 27m 53s 6m 52s 6m 52s 6m 52s 6m 52s 6m 44s 6m 44s 6m 44s 6m 44s 6m 35s 6m 35s 6m 35s 6m 35s 6m 13s 6m 13s 6m 13s 6m 13s 6h 58m 3s 6h 58m 3s 6h 58m 3s 6h 58m 3s 6h 49m 53s 6h 49m 53s 6h 49m 53s 6h 49m 53s 6h 47m 31s 6h 47m 31s 6h 47m 31s 6h 47m 31s 6h 46m 21s 6h 46m 21s 6h 46m 21s 6h 46m 21s 6h 39m 6s 6h 39m 6s 6h 39m 6s 6h 39m 6s 6h 34m 59s 6h 34m 59s 6h 34m 59s 6h 34m 59s 6h 25m 1s 6h 25m 1s 6h 25m 1s 6h 25m 1s 6h 16m 1s 6h 16m 1s 6h 16m 1s 6h 16m 1s 5m 20s 5m 20s 5m 20s 5m 20s 5h 6m 16s 5h 6m 16s 5h 6m 16s 5h 6m 16s 5h 53m 16s 5h 53m 16s 5h 53m 16s 5h 53m 16s 5h 49m 45s 5h 49m 45s 5h 49m 45s 5h 49m 45s |
In your code, remove everything after your Source line, past below
MapTbl = Record.FromList({18144000, 604800, 86400, 3600, 60, 1},{"mos","w","d","h","m","s"}), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sum(List.Transform(Text.Split([Resolution Time in Bhrs], " "), (x)=>Number.From(Text.Select(x, {"0".."9"})) * Record.FieldOrDefault(MapTbl, Text.Select(x, {"a".."z"}))))) in #"Added Custom"
4 Replies
See the below code. You will get answer in seconds. If you want in mins, divide by 60.
1 month = 30 days is the assumption
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdW7CsMwDAXQXxGZO1gPO/G3hG4dvJgOGfL7lQN9LAXl0m73IBB2LDvrOkm/b6Q76Y2skdZOWbbpelkn5u8ltMI7yVER88rPC7WRSScDqd5JF4zircQALp1KMKVQ8m2lTswAZ/+SKR61/ieKRmMj33gFwGdRfFIE0JiSGZBmp2KUcUwIy/u6RqJZPGqOR9ZobJT9lBBZ/dj2WY5xZZC+fAE5ntOCaLyQFaPk1y04JS6AvIOkaDxuGhdAWXGOUXj+MuK8PgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Resolution Time in Bhrs" = _t]), MapTbl = Record.FromList({18144000, 604800, 86400, 3600, 60, 1},{"mos","w","d","h","m","s"}), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sum(List.Transform(Text.Split([Resolution Time in Bhrs], " "), (x)=>Number.From(Text.Select(x, {"0".."9"})) * Record.FieldOrDefault(MapTbl, Text.Select(x, {"a".."z"}))))) in #"Added Custom"Hi
My source is different however how to replace my source in the exisiting codeIn your code, remove everything after your Source line, past below
MapTbl = Record.FromList({18144000, 604800, 86400, 3600, 60, 1},{"mos","w","d","h","m","s"}), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sum(List.Transform(Text.Split([Resolution Time in Bhrs], " "), (x)=>Number.From(Text.Select(x, {"0".."9"})) * Record.FieldOrDefault(MapTbl, Text.Select(x, {"a".."z"}))))) in #"Added Custom"
Thank you so much
It worked
