Index Column By Category

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