Index Column By Category

[Anonymous]

Hi all,

I have the following column

Category
Bear
Bear
Pig
Pig
Parrot
Parrot

And I want to add an index by category, like this:

Category Index
Bear 1
Bear 1
Pig 2
Pig 2
Parrot 3
Parrot 3

I know I could make a custom column with an if statement, but I need it to work dynamically. Could someone advice me on the M code? 

Best regards,

Morten 

[TeigeGao · Accepted solution]

Hi @Anonymous ,

Please refer to the following M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpNLFKK1UFiBGSmo9KJRUX5JSjMWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Category Index", each _, type table [Category=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded Category Index" = Table.ExpandTableColumn(#"Added Index", "Category Index", {"Category"}, {"Category Index.Category"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Category Index",{{"Index", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Category Index", each [Category Index.Category] &" " &[Index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Category Index.Category", "Index"})
in
    #"Removed Columns"

The result will like below:

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao and @Zubair_Muhammad , 

Thank you for that. 

I was actually looking for the following table (sorry for sloppy explenation):

Category	Index
Bear	1
Bear	1
Pig	2
Pig	2
Parrot	3
Parrot	3

But I could still use your code to create the column. 

Thank you for helping me out on this one!

Have a good day

Best ,

Morten

[Zubair_Muhammad]

Hi @Anonymous 

Using Query Editor..

You can Groupby the Category column >>> then add an index Column>>>then expand it

This way same category items will have same index number