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

[raymond]

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?

[raymond]

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]

@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 · Accepted solution]

@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 

[raymond]

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

[Geradav]

@raymond Alright, that's a good complementary information

[Geradav]

@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]

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

[raymond]

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.