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