Forum Discussion
Solved
Resolution Time in Bhrs
Hi All, I have data coming from directly from ITSM Ticketing tool and it is storing in the form of text and format is "1d 0h 55m 55s" Now we need to convert that number into hours Kindly help how...
Hi,
= Table.TransformColumns(
Table.ReplaceValue(Your_Source,null,null,
(x,y,z)=>Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(x),
{{"d","*86400"}, {"h","*3600"}, {"m","*60"}, {"s",""}, {" ","+"}})),
{"Resolution Time in Bhrs"}),
{{"Resolution Time in Bhrs", each Expression.Evaluate(_)/3600, type number}})Stéphane
Hi,
1mos 1w 2d 4h 24m 5s = 950,40 hours
let
Source = #table({"Resolution Time in Bhrs"},{{"1mos 1w 2d 4h 24m 5s"}}),
Transform = Table.TransformColumns(
Table.ReplaceValue(Source,null,null,
(x,y,z)=>Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(Text.Replace(x,"mos","*365/12*24")),
{{"w","*7*24"},{"d","*24"}, {"h",""}, {"m","/60"}, {"s","/3600"}, {" ","+"}})),
{"Resolution Time in Bhrs"}),
{{"Resolution Time in Bhrs", Expression.Evaluate, type number}})
in
Transform
Stéphane
Hi
We need a calcualated column instead of a cumlative number
Can you help help in getting calulatation in invidual row wise
i am attaching pbix for the same
Data will be coming in the below format hence
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 You can duplicate column and then perform transformation 🙂
Hi,
= Table.AddColumn(
YourSource,
"NewColumn",
each Expression.Evaluate(
Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(Text.Replace([Resolution Time in Bhrs],"mos","*365/12*24")),
{{"w","*7*24"},{"d","*24"}, {"h",""}, {"m","/60"}, {"s","/3600"}, {" ","+"}}
)
)
)
)Stéphane
Still getting this error
