Sort on a calculated column

[Anonymous]

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?

[Greg_Deckler]

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]

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]

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"

[Anonymous]

Thanks very much @Greg_Deckler that gets me the surname column - how would I go about retaining the Full Name column as well (as I want to display the full name by sort by surname).

Apologies - I'm a newbie to PowerBI and haven't edited queries in this way before!

[Greg_Deckler]

Minor change:

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}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), type text)in
    #"Inserted Text After Delimiter"

[Anonymous]

Nearly there... I'm getting an error as my "Name" column is actually called something different ("Full Name") so I've changed the bits in blue below

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,{{"Full Name", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Full Name], " ", {0, RelativePosition.FromEnd}), type text)in
    #"Inserted Text After Delimiter"

However when I do that it says "Invalid identifier" (whether I use ["Full Name"] or [Full Name]) - am I missing some syntax?