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 des...
Need Help
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

LPriyanka's avatar
LPriyanka
New Member
3 years ago

can you please help me with eample

  • 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