Forum Discussion
Sort on a calculated column
Community ChampionSo, 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...
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"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"