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 

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

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      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

       

  • 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