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

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

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!

 

1 ACCEPTED SOLUTION
Jakinta
Super User
Super User

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

Jakinta_0-1623388269017.png

 

View solution in original post

4 REPLIES 4
Jakinta
Super User
Super User

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

Jakinta_0-1623388269017.png

 

View solution in original post

Yannyqi
Frequent Visitor

Hi @Jakinta ,

 

Thank you very much for the help.

 

CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-06-10 211144.png

Yannyqi
Frequent Visitor

Hi @CNENFRNL ,

 

Thank you very much for the help.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors