Need help to remove few words within a column in Power BI

[Anonymous]

Hi everyone,

I am new to power bi and i would like to know if there is any way to remove some words within the whole column?

For eg. The variables within the property row are:

1. Entire condo
2. Private room in apartment
3. Room in hotel
4. Loft
5. Room in apartment
6. Shared room in condo
7. Private room in hotel
8. Tiny house
9. Shared room
10. Private room

I would like to remove the words "Entire",  "Private room in",  "Room in", "Shared room in"

Thank you very much in advance!

[Jakinta · Accepted solution]

Here is another one...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0rySxKVUjOz0vJV4rViVYKKMosSyxJVSjKz89VyMxTSCxILCrJTc0rAcsGQUUz8ktSc8AiPvlpqFKoGoIzEotSU+Cm4bYHYWJIZl4lkFtanIpuAoZGpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OldText = _t]),
    Words = {"Entire", "Private room in", "Room in", "Shared room in"},
    FINAL = Table.AddColumn(Source, "NewText", each 
            Text.Trim (List.Accumulate( {0..List.Count(Words)-1}, [OldText], 
            (s,c) => Text.Replace( s, Words{c}, "" ))))
in
    FINAL

[Anonymous]

Hi @Jakinta ,

Thank you very much for the help.

[CNENFRNL]

let
    Kw = {"Entire",  "Private room in",  "Room in", "Shared room in"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0rySxKVUjOz0vJV4rViVYKKMosSyxJVSjKz89VyMxTSCxILCrJTc0rAcsGQUUz8ktSc8AiPvlpqFKoGoIzEotSU+Cm4bYHYWJIZl4lkFtanIpuAoZGpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),
    #"Added Custom" = Table.AddColumn(Source, "New", each List.Accumulate(Kw, [Type], (s,c) => let pos=Text.PositionOf(s,c,1,Comparer.OrdinalIgnoreCase) in if pos<>-1 then Text.Trim(Text.RemoveRange(s,pos,Text.Length(c))) else s))
in
    #"Added Custom"

[Anonymous]

Hi @CNENFRNL ,

Thank you very much for the help.