Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Index Column By Category

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 

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    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

3 Replies

  • TeigeGao's avatar
    TeigeGao
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi TeigeGao and Zubair_Muhammad , 

       

      Thank you for that. 


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

       

      CategoryIndex
      Bear1
      Bear1
      Pig2
      Pig2
      Parrot3
      Parrot3

       

      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's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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