cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cgkas Helper IV
Helper IV

How to add sequence number to group of same words?

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:

 

WORDSSORTED WORDS
BBBBBBBB_1
GGGGBBBB_2
DDDDBBBB_3
BBBBCCCC_1
GGGGDDDD_1
CCCCDDDD_2
KKKKDDDD_3
CCCCDDDD_4
GGGGGGGG_1
CCCCGGGG_2
KKKKGGGG_3
CCCCGGGG_4
KKKKGGGG_5
DDDDKKKK_1
BBBBKKKK_2
KKKKKKKK_3
DDDDKKKK_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"

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
MariusT Advocate I
Advocate I

Re: How to add sequence number to group of same words?

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]

View solution in original post

Super User IV
Super User IV

Re: How to add sequence number to group of same words?

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
MariusT Advocate I
Advocate I

Re: How to add sequence number to group of same words?

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]

View solution in original post

cgkas Helper IV
Helper IV

Re: How to add sequence number to group of same words?

@MariusT 

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

Super User IV
Super User IV

Re: How to add sequence number to group of same words?

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

cgkas Helper IV
Helper IV

Re: How to add sequence number to group of same words?

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

Super User IV
Super User IV

Re: How to add sequence number to group of same words?

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors