Help with function to clean up data - replace

[JuanBolas]

Hello,

I'm new to Power BI and Power Query.

I want to clean up my data a little bit before going into BI.

My table has about a dozen fields but I just want to concentrate on one called Consignee which is a string and there are variations of the same string like

Input                                                Desired Output

------------------------                     ------------------------------

NameOfCompany S.A.                     NameOfCompany

Name of Company SA                     NameOfCompany

Name of Company  SA                    NameOfCompany

Company2 S.R.L.                              Company2

Company 2 SRL                               Company2

Company 2  SRL                              Company2

Company 2 Ltda.                             Company2

Company 2 Ltda                              Company2

Company 2 S R L                             Company

Asst. Jones                                       Asst Jones

To get an idea of who the clean data would look I did some tests in Excel.  What I ended up doing in Excel to clean up the data was to:

0) select column 'Consigne'

1) search and replace "." with " " (space) - beacuse there are other abbreviations that need the space after them

2) search and replace  "  " (2 spaces) with " " (one space)

3) search and replace " S A " with "SA"

4) search and replace " S R L  " with "SRL"

5) search and replace "Ltda" with "SRL"

6) search and replace "&" with "&"

7) trim

I turned this into a macro.

Now when I try to do something like this in Power Query I get stuck at replacing "." with " ". I don't believe that I should add a column for each replacement.  There has got to be a better way.

Then what I do is a fuzzy search and merge to add product category names from a table that has the 'Consigne' and 'Category'.

Thanks in advance for your help

[AlexisOlson · Accepted solution]

Use the Transform tab rather than the Add Column tab if you don't want a new column for each step.

[AlexisOlson · Accepted solution]

Use the Transform tab rather than the Add Column tab if you don't want a new column for each step.

[JuanBolas]

Worked great @AlexisOlson.

How do you suggest I remove the trailing SA and SRL at the en of some names? SRL works ok with replace but a replace of SA or " SA" (space SA) changes things that shouldn't be changed.

Thanks in advance

[AlexisOlson · Accepted solution]

You might need to write some more customized logic like this:

= Table.TransformColumns(
    #"Replaced Value4",
    {{"Input", each
      if Text.End(_, 3) = " SA" or Text.End(_, 4) = " SRL"
      then Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd})
      else _, type text}}
)

[JuanBolas]

Thanks a million @AlexisOlson