Add unique ID column

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 !

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