Replace [textstring (the)] with [The textstring]

[Anonymous]

Hello,

I have a data source with one column that lists product names, some of which contain elements that have the "The" formatted at the end or in the middle of the string, and I need them at the beginning.

For example:

Beatles, the: Complete Collection --> The Beatles: Complete Collection

Allman Brothers Band (the): 2CDs --> The Allman Brothers Band: 2CDs

Not all of the elements have "The", and it can be formatted in five different ways:

• , the
• , The
• , (the)
•  (The)
•  (the)

...so basically I need to find any of the above five elements, replace with "" AND insert "The " at the beginning of the string in one of the Power Query steps. Can this be done?

Thanks

[ImkeF]

Sounds doable, please check this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcqxCoAgFAXQX3k4FTg1uqX9QaM4SF1SeGro+3+StjMc75VFFMbQJAmGXCsvQzDBjEtyqypor3bmEivZ3mbrg2ysNy3Tq6HNHeNPZyuQlOtD4AEVwgc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ListOfKeywords = {", the", ", The", ", (the)", "(The)", "(the)"},
    PositionKeyword = Table.AddColumn(Source, "PositionKeyword", each List.PositionOf(List.Transform(ListOfKeywords, (x) => Text.Contains([Column1], x)), true)),
    Result = Table.AddColumn(PositionKeyword, "Result", each if [PositionKeyword] <> -1 then "The " & Text.Replace([Column1], ListOfKeywords{[PositionKeyword]}, "") else [Column1])
in
    Result