delete everything before the last transcription of a string

[Anonymous]

Hello,

Data = "aaaa eeee rrrr ttttt yyyy uuuu aaaa ffff hhhh aaaa cccc"

I'm trying to delete everything before the last "aaaa" on each cell.

Output "line 4" should be "aaaa cccc"

Output "line 5" should be "aaaa"

Thanks!

Amine

[Anonymous]

Worked thanks!

[AntonioM · Accepted solution]

Hi @Anonymous ,

You could try

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

Two steps here. First extract everything after the first "aaaa" from the end and then add "aaaa " back to the front.