Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
raymond
Post Patron
Post Patron

Power Query: remove all words with more than 3 characters in one cell

Hello,

 

I am doing some text clean up work and want to dynamically delete all words with up to 3 characters. I have therefore created a dynamic list of words with more than 3 characters. 

 

However I cannot replace it with that list using the replace button in power query. Does anyone have an idea?

1 ACCEPTED SOLUTION
Geradav
Responsive Resident
Responsive Resident

@raymond 

See below a potential solution

Table.TransformColumns(
    #"name of previous step",
    {
        "columnName",
        each 
            let
                vTextSplit = Text.Split(_, " "),
                vListSelect = List.Select(vTextSplit, each Text.Length(_) >3),
                vResult = Text.Combine(vListSelect, " ")
            in vResult,
        type text
    }
)

Let us know if that works for you.

 

David 

View solution in original post

8 REPLIES 8
raymond
Post Patron
Post Patron

Thank you guys.

The think is there are mutliple words in one cell. So I have a text as follows "the way can be good for business" I want it to return "good business" only. 

Geradav
Responsive Resident
Responsive Resident

@raymond if you want the result in a different column, then use the following

Table.AddColumn(
    #"name of previous step",
    "newColumnName",
    each 
        let
            vTextSplit = Text.Split([columnName], " "),
            vListSelect = List.Select(vTextSplit, each Text.Length(_) >3),
            vResult = Text.Combine(vListSelect, " ")
        in vResult,
    type text
)
Geradav
Responsive Resident
Responsive Resident

@raymond 

See below a potential solution

Table.TransformColumns(
    #"name of previous step",
    {
        "columnName",
        each 
            let
                vTextSplit = Text.Split(_, " "),
                vListSelect = List.Select(vTextSplit, each Text.Length(_) >3),
                vResult = Text.Combine(vListSelect, " ")
            in vResult,
        type text
    }
)

Let us know if that works for you.

 

David 

@Geradav wow, that was so quick and works so well. Thank you!

Geradav
Responsive Resident
Responsive Resident

@raymond Alright, that's a good complementary information

Geradav
Responsive Resident
Responsive Resident

@raymond I think you could adjust the M code with something as follow in the column where you want to get rid of your "3 characters" text:

Table.TransformColumns(
    #"name of previous step",
    {
        "columnName",
        each if Text.Length(_) <= 3 then "" else _
    }
)

 

Let us know if that works for you

 

David 

PhilipTreacy
Super User
Super User

Hi @raymond 

Do you want to keep words with more than 3 chars or remove them?  Your problem title and the question text say different things.

You can use a step like this to keep rows where  [ColumName] contains words with 3 or fewer characters

 

= Table.SelectRows(#"PreviousStep", each Text.Length([ColumnName]) <=  3)

 

But if this isn't what you want, please post some sample data and examples of your expected result.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks @PhilipTreacy 

you are right, could be more specific. 

 

The sample data is as follows:

Original Column A includes:
"the way can be good for business" 

 

Expected Column B should result: 

"good business"

 

Delete all words with less than 3 characters and keep the ones with more.

 

I was thinking of you a replacer can iterate through a list of values and replaces them but wasnt able to find it. Perhaps there is another solution.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.