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 Par...
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

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