changing data type from text to 9m 10d formate

[LPriyanka]

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.
 

[m_dekorte]

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

[LPriyanka]

can you please help me with eample

[m_dekorte]

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