Forum Discussion
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
Use the Transform tab rather than the Add Column tab if you don't want a new column for each step.
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}} )
4 Replies
Use the Transform tab rather than the Add Column tab if you don't want a new column for each step.
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}} )
