How to add sequence number to group of same words?

[cgkas]

Hello everyone,

I have the column "Words" that is the input and I´d like to get the strings in this column, sorted alphabetically and adding a sequence number in such way that the resulting Column "Sorted Words" be like below:

WORDS	SORTED WORDS
BBBB	BBBB_1
GGGG	BBBB_2
DDDD	BBBB_3
BBBB	CCCC_1
GGGG	DDDD_1
CCCC	DDDD_2
KKKK	DDDD_3
CCCC	DDDD_4
GGGG	GGGG_1
CCCC	GGGG_2
KKKK	GGGG_3
CCCC	GGGG_4
KKKK	GGGG_5
DDDD	KKKK_1
BBBB	KKKK_2
KKKK	KKKK_3
DDDD	KKKK_4

My current codes does the easiest part, sort the column, but I don´t know how to add the sequence number to each group of words.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgICpVidaCV3IAAzXIAAzMCUcgYCMMMbCFBFiFEDF8G0Ak0qFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WORDS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WORDS", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"WORDS", Order.Ascending}})
in
    #"Sorted Rows"

[Anonymous · Accepted solution]

This article show you how to create row numbers for groups.

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

After doing this you can create a custom column [SORTED WORDS]&"-"&[RowNumberColumn]

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"WORDS"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [WORDS]&"_"&Number.ToText([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"WORDS", Order.Ascending}, {"Custom", Order.Ascending}})
in
    #"Sorted Rows"

Hope this helps.

[cgkas]

@Ashish_Mathur Thanks so much for your help. It helps a lot.

[Ashish_Mathur]

You are welcome.

[Anonymous · Accepted solution]

This article show you how to create row numbers for groups.

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

After doing this you can create a custom column [SORTED WORDS]&"-"&[RowNumberColumn]

[cgkas]

@Anonymous 

Thanks so much for the link shared. It help me to get the desired output.