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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.