Creating unique value if value already exists

[Nipius]

Hi all!

I have a query including a column with duplicate values. For the purpose of my table, I'd like to add another column where I add another character (for example "A" or "1") to the duplicate of a value.

For example, in case of

58379

58379

58379

58379

93850

39412

39412

I'd like the first value to keep as is, but change the other duplicates and ignore the unique values. The above would then result in

58379

58379A

58379AA

58379AAA

93850

39412

39412A

Is there a way for me to accomplish this? Thanks in advance!

[parry2k]

@Nipius copy these steps into your table using query editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrUwNrdUitUhxLI0tjA1ALOMLU0MjZBZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Count", each  Table.AddIndexColumn(_, "Rank", 0)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Rank"}, {"Rank"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Rank", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "New Id", each Number.ToText([Id]) &
(
if [Rank] > 0 then "-" & Number.ToText([Rank]) else ""
)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Id", "Rank"})
in
    #"Removed Columns"

[Nipius]

Thanks @parry2k !

I've added this as a Custom Column, but there is not a reference to the particular column I want to get the values from. It now shows me the numbers I mentioned below, which was actually just an example. Am I missing something? 🙂

[parry2k]

@Nipius if you share your full sample dataset it will help, I just gave you the solution based on what you posted originally.

[Nipius]

@parry2k : Sure! Herewith the link to the file. It's on my personal NAS, as my organization does not allow to create shareable OneDrive links for persons outside the organization.

I've added the Position_Unique column manually for your reference, but this is in the end the result I'd like to get. Hopefully this makes sense.

[parry2k]

@Nipius trying to download the file, stay tuned.

[parry2k]

@Nipius you also have to share xlsx file as I need it to perform transformation in power query

[Nipius]

@parry2k There you go: http://gofile.me/6C9E1/dgz5x8Ldn

[parry2k · Accepted solution]

@Nipius here you go, see attached, I added two unique columns one with _1, _2, _3 and so on and other with _A, _AA, _AAA and so on.

[Nipius]

@parry2k Thanks a ton! This is exactly what I was looking for. Many thanks!