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 

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