Resolution Time in Bhrs

[pdindukurthi]

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 

[slorin]

Can you share your code, not an image
Stéphane

[pdindukurthi]

I 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}})

[slorin]

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 

[pdindukurthi]

Hi 
I am still getting error 

[slorin · Accepted solution]

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 

[pdindukurthi]

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 

[pdindukurthi]

HI 

Could you please help me, waiting for the reply 

[slorin]

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

[pdindukurthi]

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 

[pdindukurthi]

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

[slorin]

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 

[pdindukurthi]

Still getting this error 

[dufoq3]

You can duplicate column and then perform transformation 🙂

[pdindukurthi]

hi 

thank you so much 
However should i do this step in powerquery or normal dax editor

[dufoq3]

Hi @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