Renaming similar names

Power Query
1

Hi Team,

I hope that you are all doing great. I have a question that is a little tricky. I am getting data from 2 systems and the naming is different. In the below example, Verona Loxton is the same as “The estate of Mrs Verona Loxton”. I want a change that when name “Verona Loxton” appears, we should change it to the name to “The estate of Mrs Verona Loxton”. I want to create a new column similar to the attached snapshot. How to achieve below in Power Query/DAX?

image
image800×196 4.92 KB

Kind Regards,
Naila

2

Hi @Naila,

Welcome to the forum !
In order to help you more easily, do you have a PBIX file ?

Best regards,
Joaly

1 Like
3

Hi @Naila,

Welcome to the Forum!

Give something like this a go, in Power Query choose “add custom column” and insert the if-then-else statement. Once you’ve pressed ‘okay’ you should see this code in the formula bar (where ‘Source’ will have the Name of the Previous Step in your code).

image

I hope this is helpful

5 Likes
4

This is a great use case for the “Add Column from Example” feature of PowerQuery. Sam did a great video on it not long ago.

Column From Examples - My Favorite Feature Within The Power Query - YouTube

The end result will likely be the same code that @Melissa provided, but it’s just a different way of getting there.

3 Likes
5

Hi @Melissa , @DaveC ,@JBocher ,

Thank you so much for providing solution. But my data is bit complex. Name can appear in 3 forms:

  1. Only name with salutation( like Estate of Annie Ross)
  2. Name with Mr/Mrs ( like Estate of Mr Gary Ryan)

So, in given below example, even after implementing your solution, I end up with 2 different names although these are same. I tried with the suggestion given by @DaveC from column from example, but it did not work out too.

image
image1152×446 18.8 KB

Is there any way where we can check if that name appears as subset of name in new row to mark it as new name? Sample PBI file attached.

Sample estate file.pbix (14.7 KB)

Kind Regards,
Naila

7

Hi @Naila,

Can you please share with us your Excel file “testing123” too ?
We can’t work on Power Query without it…

Best regards,
Joaly

8

Hi @Naila,

Think I’ve got this sored, try this (copy the full script into a new blank query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkstys9T8MmvKMnPU4rViVZyzMvLTFUIyi8uBnPdE4sqFYIqEyGSIRmpCq7FJYklqQr5aQq+RcUKYP2JyAagqnFEMQ1dvwKS8bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    AddRecord = Table.AddColumn(Source, "Record", each 
        [ 
            Prefix = let strings = List.Transform( Text.Split([Name], " "), each Text.Proper(_) & " ") in if List.ContainsAny( strings, { "Mr ", "Mr. " }) then "Mr" else if List.ContainsAny( strings, { "Mrs ", "Mrs. " }) then "Mrs" else "",
            Estate = if Text.Contains([Name], "The Estate of", Comparer.OrdinalIgnoreCase) then "The Estate of " else null,
            Name only = if (Text.Contains([Name], "The Estate of", Comparer.OrdinalIgnoreCase) and Text.Length( Prefix) <> 0 ) then Text.Trim(Text.Range([Name], 17 )) else if (Text.Contains([Name], "The Estate of", Comparer.OrdinalIgnoreCase) and Text.Length( Prefix ) = 0 ) then Text.Range([Name], 14 ) else [Name]
        ] ),
    SortRows = Table.Buffer( Table.Sort( Table.ExpandRecordColumn( AddRecord, "Record", {"Prefix", "Estate", "Name only"}), {{"Name only", Order.Ascending}, {"Prefix", Order.Descending}})),
    GroupAndTransformRows = Table.Group(SortRows, {"Name only"}, {{"AllRows", each Table.FillDown( Table.FromColumns( {_[Name], List.ReplaceMatchingItems( _[Prefix], {{"", null}})}, {"Name", "Prefix"}), {"Prefix"}), type table }}),
    CombineStrings = Table.AddColumn( Table.ExpandTableColumn(GroupAndTransformRows, "AllRows", {"Name", "Prefix"}, {"Name", "Prefix"}), "Custom", each Text.Combine( {"The Estate of", [Prefix], [Name only] }, " "), type text)[[Name], [Custom]]
in
    CombineStrings

.
Note that some names don’t match but when you fix the type-o’s it will return the desired result.

image

I hope this is helpful

2 Likes
9

Hi @Melissa,

This is absolutely brilliant solution and this is exactly what I wanted. Thank you so much for providing such an efficient solution. I am unable to completely understand M code in this part. However, you did not get the source to excel file then how did you manage to bring names as you did not manually define names in M-code?

Thanks again and your support.

Kind Regards,
Naila

10

Hi @Naila,

Since you had a small set, I could copy and paste your sample into the “Enter Data” window. Also did a video on that recently, you can find that here.

1 Like
11

Hi @Melissa ,

Very nicely explained video. I have watched it and it’s all clear now. Thanks you so much for this amazing support.

Kind Regards,
Naila

1 Like