Power Query Editor Flag Duplicates - return "Unique" if name is unique in column and "Duplicate" if it is repeated:

Question

for example I have a list of names:

1. Bob
2. Bob
3. Greg
4. Joe

Expected results:

1. Bob Duplicate
2. Bob Duplicate
3. Greg Unique
4. Joe Unique

I have done this already by doing a count on query editor then counting anything > 1 to give me the results but surely theres an easier way! (1 step process) Thank you in advance :)

Answer 1

I have a solution to propose, but you will probably found it long. Maybe there is something shorter and more efficient.

I called your column with this list of name "Name of people".

First of all I added an index.

I then grouped by the column of people with names.

The grouping indicate, if it is above 1, that this is a duplicate.

I then added a column which combines the person name and either "unique" or "duplicate" if we have a 1 or above 1.

I have the following power query:

 #"Grouped Rows" = Table.Group(#"Added Index", {"Name of people"}, {{"Duplicates", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Duplicate yes/no", each if [Duplicates] <> 1 then 
Text.Combine({[Name of people]," Duplicate"}) else 
Text.Combine({[Name of people]," Unique"}))
in
    #"Added Custom"

Answer 2

Here's another way. With this table as my Source:

I added a new custom column:

To get: