cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mi20100 Visitor
Visitor

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

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

1 REPLY 1
Highlighted
Super User
Super User

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries