Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Sort on a calculated column

I've got a column that contains people's full names and have extracted their surnames out to a new column using the following calculation:

 

    Surname =
    VAR Nodes = SUBSTITUTE( [Full Name], " ", "|" )
    RETURN
    PATHITEM( Nodes, PATHLENGTH( Nodes ) )

 

which gives me Surnames in another calculated column.

 

I then want to use [Surname] to sort [Full Name] (as Full name also includes titles and initials (e.g. Mr J P Bloggs) so if I sort by that then it's no good) but I get the following error message: "This column can't be sorted by another column that is already sorted, directly or indirectly, by this column".

 

Is there a way round this?

 

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    So, I believe what you need to do is to perform the extraction in the Query Editor. However, that might cause another issue where you do not have a one-to-one match between your columns...

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Yes I've avoided doing it via the query editor as the names are in different formats (e.g. JP Bloggs, Mr J P Bloggs, Mr Jonathan P Bloggs) and I don't think I can just extract the last word in the column can I? I'll go have a closer look though...

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        Sure you can, use something like this:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gpQcMrJT08vVorViVbyLVLwUkCIxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Name", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}})in
    #"Extracted Text After Delimiter"