Forum Discussion
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 to proceed further on this
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
15 Replies
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
thank you so much
However should i do this step in powerquery or normal dax editorHi pdindukurthi, It is Power Query solution.
Here you have another one (but slorin's version is more elegant)
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMstQMDbLVTA1KVaK1YlWMkxRMM9QsMhVMDaFCuQqQKVMLSG0WYqCkVGGgqFxroIZVI0RxBBDkIpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Resolution Time in Bhrs" = _t]), ToList = Table.AddColumn(Source, "Lst", each Text.Split([Resolution Time in Bhrs], " "), type list), Ad_SeparateColumns = List.Accumulate( Text.ToList("dhms"), ToList, (s,c)=> Table.AddColumn(s, c, each Number.From(Text.BeforeDelimiter(List.Select([Lst], (x)=> Text.Contains(x, c)){0}?, c)), Int64.Type) ), ReplacedValue = Table.ReplaceValue(Ad_SeparateColumns,null,0,Replacer.ReplaceValue,{"d", "h", "m", "s"}), Ad_Duration = Table.AddColumn(ReplacedValue, "Duration", each #duration([d], [h], [m], [s]), type duration), Ad_TotalHours = Table.AddColumn(Ad_Duration, "Total Hours", each Duration.TotalHours([Duration]), type number), RemovedOtherColumns = Table.SelectColumns(Ad_TotalHours,{"Resolution Time in Bhrs", "Duration", "Total Hours"}) in RemovedOtherColumns
Hi Stephane,
Good Morning
Have an issue now with the above given soultion
Now i am getting 1mos 1w 2d 4h 24m 5s and i have tried to rewrite the above quwery as below
= Table.TransformColumns(
Table.ReplaceValue(Table.Combine({Inc_Sr_Inflow, Inc_Sr_Closed}),null,null,
(x,y,z)=>Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(x),
{{"mos","*43800"},{"w","*10800"},{"d","*1440"}, {"h","*60"}, {"m","*1"}, {"s",""}, {" ","+"}})),
{"Resolution Time in Bhrs"}),
{{"Resolution Time in Bhrs", each Expression.Evaluate(_)/3600, type number}})However i am getting exponential values which powerbi is considering it as errors
Kindly help on the same
HI
Could you please help me, waiting for the reply
Hi
= Table.TransformColumns(
Table.ReplaceValue(Table.Combine({Inc_Sr_Inflow, Inc_Sr_Closed}),null,null,
(x,y,z)=>Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(Text.Replace(x,"mos","*43800")),
{{"mos","*43800"},{"w","*10800"},{"d","*1440"}, {"h","*60"}, {"m","*1"}, {"s",""}, {" ","+"}})),
{"Resolution Time in Bhrs"}),
{{"Resolution Time in Bhrs", each Expression.Evaluate(_)/3600, type number}})Stéphane
Hi
I am still getting error
Can you share your code, not an image
StéphaneI am using the same code which was shared with me
= Table.TransformColumns(
Table.ReplaceValue(Table.Combine({Inc_Sr_Inflow, Inc_Sr_Closed}),null,null,
(x,y,z)=>Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(Text.Replace(x,"mos","*43800")),
{{"w","*10800"},{"d","*1440"}, {"h","*60"}, {"m","*1"}, {"s",""}, {" ","+"}})),
{"Resolution Time in Bhrs"}),
{{"Resolution Time in Bhrs", each Expression.Evaluate(_)/3600, type number}})
