Add unique ID column

[M_SBS_6]

Hi, 

I have a table with a text output and with my client I could have multiple rows. What I need to do is assign them a.  unique number from 1 onwards. Any idea if this can be done please?

Example 

Unique no.  Client 

    1                   FTY

    1                   FTY

  1                     FTY

  2                      Abat

  3                       Keys

   3                       Keys

   4                        Coast

5.                         Docks

[serpiva64 · Accepted solution]

Hi,

you can use remove duplicates on your column

and then add an index column (from 1 or from 0 as you prefer)

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[PijushRoy · Accepted solution]

Hi @M_SBS_6 

Please create a blank table in Power Query - Go to Advance Editor and paste the below code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcguJVIrVwaQdkxJLwAzv1MpiVIZzfmIxRM4lPzkbKBYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Client"}, #"Changed Type", {"Client"}, "Added Index", JoinKind.LeftOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Client"}, {"Client.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index",{"Client"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Client.1", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Client.1", "Client"}})
in
    #"Renamed Columns"

Source Data

Result

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[serpiva64 · Accepted solution]

Hi,

you can use remove duplicates on your column

and then add an index column (from 1 or from 0 as you prefer)

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !