Calculate sequential count of unique id in new field

[dbaber]

Hello,

I am trying to sequentially number each time a repeaded ID is listed in a column. The table I am working with does not have a Key Field. To create a key field I would like to take the repeated ID in Column A and the the sequential number in Column B so I can create a concatentated unique field.

ID	# of times the value occurs	Concatentated Key Field
1000	1	10001
1000	2	10002
1000	3	10003
2000	1	20001
3000	1	30001
3000	2	30002
3000	3	30003
4000	1	40001
4000	2	40002
4000	3	40003
5000	1	50001

I've tried using Count, Distinctcount and Countrow functions, but I'm missing something.  

Thanks!

@dbaber

[Daryl-Lynch-Bzy · Accepted solution]

Hi @dbaber - I have put together are example of how Power Query Table.Group function can be used to add index column based on the ID column.  To help you understand further please video this video on advanced Table.Group features: Advanced Group By Tricks in Power Query - YouTube

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFCK1cHGMIIxjDEYJrgZpmBGLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Add Normal Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Group Row With ID Index" = Table.Group(#"Add Normal Index", {"ID"}, {{"ID Index", each Table.AddIndexColumn( _ , "ID Index", 1 , 1 ), type table [ID=nullable text, Index=Int64.Type, ID Index=Int64.Type]}}),
    #"ID Index" = Table.Combine( #"Group Row With ID Index"[ID Index] ),
    #"Add Final Index" = Table.AddColumn(#"ID Index", "Final ID Index", each Number.From([ID]) + [ID Index], Int64.Type)
in
    #"Add Final Index"