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 ...
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"
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 code
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"