cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuanBolas
Helper II
Helper II

Help with function to clean up data - replace

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

 

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

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

AlexisOlson_1-1660750883632.png

View solution in original post

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}}
)

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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

AlexisOlson_1-1660750883632.png

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

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}}
)

Thanks a million @AlexisOlson 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors