Forum Discussion

LPriyanka's avatar
LPriyanka
New Member
3 years ago

changing data type from text to 9m 10d formate


i am having a duration column name  having values in the formate like "9m 10d " in text datatype. I want to show this column values in sort by Desc order. But in report view it is not sorted by desc because of data type , i  tried changing the data type in query editor it results error. can anyone help me with this issue by changing the data type.
 

Need Help

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi LPriyanka,

     

    You can add a numerical sort column, something like:

     

    Table.AddColumn( PrevStepNameHere, "sort duration", each 
  List.Sum(
    List.Transform(
      Text.Split([duration], " "), each 
        if Text.Contains(_, "m") 
        then Number.From(Text.Select(_, {"0" .. "9"})) * 30
        else Number.From(Text.Select(_, {"0" .. "9"}))
    )
  ), Int64.Type
)

     

    Amend to your needs.

    I hope this is helpful

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        3 years ago

        Hi LPriyanka,

         

        Sure, here you go.

        Copy this script into a new blank query

        let
    Source = Table.FromColumns(
        {{"9m 1d", "6m 28d", "9m 18d", "10m 4d"}},
        type table [#"duration" = text]
    ),
    AddDurationSort = Table.AddColumn( Source, "sort duration", each 
        List.Sum( 
            List.Transform( Text.Split([duration], " "), each 
                if Text.Contains(_, "m") 
                then Number.From( Text.Select(_, {"0".."9"}))*30 
                else Number.From( Text.Select(_, {"0".."9"}))
            ) 
        ), Int64.Type 
    )
in
    AddDurationSort

         

        Cheers