Forum Discussion

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

    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 

slorin's avatar
slorin
Super User
2 years ago

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

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

    HI 

    Could you please help me, waiting for the reply 

    • slorin's avatar
      slorin
      Super User
      2 years ago

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

        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