none
Index starting with 1 for each category in Power Query? RRS feed

  • Question

  • Hi there, 

    I have a table as below, sorting by column named "category". I am trying to create an index column that will start at 1 for the first row of each category (Index column). Is there any way that I can do it in Power Query?

    
    Index Category
    1 A
    2 A
    3 A
    4 A
    5 A
    1 B
    2 B
    3 B
    4 B
    5 B
    1 C
    2 C
    3 C
    4 C
    1 D
    2 D
    3 D
    4 D
    5 D

    I really appreciate for your help.

    Thanks, 

    Giang


    • Edited by Tong Giang Wednesday, June 28, 2017 3:36 PM
    Wednesday, June 28, 2017 3:35 PM

Answers

  • Hi Tong. There are a few different ways to do this. The most performant would probably be to group by Category, addding an index column to the rows as you group, and then expand the results.

    For example, here's an Advanced Editor script to do just that:

    let
        Source = ...,
        #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1), type table}}),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Category", "Index"}, {"Rows.Category", "Rows.Index"})
    in
        #"Expanded Rows"

    Ehren

    • Marked as answer by Tong Giang Thursday, June 29, 2017 12:24 AM
    Wednesday, June 28, 2017 7:07 PM
    Owner

All replies

  • Hi Tong. There are a few different ways to do this. The most performant would probably be to group by Category, addding an index column to the rows as you group, and then expand the results.

    For example, here's an Advanced Editor script to do just that:

    let
        Source = ...,
        #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1), type table}}),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Category", "Index"}, {"Rows.Category", "Rows.Index"})
    in
        #"Expanded Rows"

    Ehren

    • Marked as answer by Tong Giang Thursday, June 29, 2017 12:24 AM
    Wednesday, June 28, 2017 7:07 PM
    Owner
  • Hi Ehren.

    Thank for your help. It works!

    Best regards, 

    Giang

    Thursday, June 29, 2017 12:29 AM