Forum Discussion

pdindukurthi's avatar
pdindukurthi
Icon for Helper I rankHelper I
2 years ago
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 ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
2 years ago

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's avatar
pdindukurthi
Icon for Helper I rankHelper I
2 years ago

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

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    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"