Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

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



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) 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?



Super User III
Super User III

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

Sounds doable, please check this code:


    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])
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries


Helpful resources

New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors