Need a calculated column based on the existing column

[pdindukurthi]

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

[pdindukurthi]

Thank you so much 

It worked 

[Vijay_A_Verma]

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"

[pdindukurthi]

Hi 
My source is different however how to replace my source in the exisiting code 

[Vijay_A_Verma · Accepted solution]

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"