Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LPriyanka
New Member

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.
 Screenshot 2023-04-11 004522.png

3 REPLIES 3
m_dekorte
Super User
Super User

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

can you please help me with eample

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors