If statement to replace specific value

[DSwezey]

I have a table with Employee names.

First Name 

Last Name

Name Code (First initial Last name all caps)

Example:

First Name: Dan

Last Name: Smith

Name Code: DSMITH

There are some employees with the same last name and same first initial resulting in them having the same Name Code. 

Example:

First Name: Damien

Last Name: Smith

Name Code: DSMITH

I want to replace one of the Name Codes in the column to be unique. I cannot just go into the column and replace value for "DSMITH" as it will change both.

My idea was to add another column in the table with an IF;

= if ([firstName] = "Dan" and [lastName] = "Smith") then ([Name Code] = "DASMITH" else [Name Code])

But this is resulting in the Name Code for Dan Smith to be "False"

No sure how to go about this. 

[Anonymous]

Hi @DSwezey ,

Could you tell me if your problem has been solved by @Vijay_A_Verma 's method? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Vijay_A_Verma]

My suggestion would be following. If names are repeated append this with a number to provide uniqueness.

Hence if there are

Dan Smith

Dana Smith

Daniel Smith

In your approch 2nd and 3rd will have same DASMITH.

In my approach, 2nd and 3rd will be DSMITH2 and DSMITH3.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcxLCoQwDIDhu2TtKbRCBy2V6q64iBo0+KiUev+JztbZJOGHfN6DwgMyaHdOi2zVmk+noc881HhFlOTCILN2Nn+ywp3pz4vhlaRoGlOIchhdFp11P46P6Y3jeaCY3r0K43Z7IZx0e5W2tinF678=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Name Code" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    NameBuffList=#"Added Index"[Name Code],
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [NameCount=List.Count(List.Select(List.FirstN(NameBuffList,[Index]), (x)=>x=[Name Code])),
FinalNameCode=if NameCount>1 then [Name Code]&Text.From(NameCount) else [Name Code]][FinalNameCode]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Name Code"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Name Code"}})
in
    #"Renamed Columns"