none
Restart numbering for each change in

    Question

  • Hi,

    Let's say, in the Power Query window, I have A,B,A,C,A.  In another column, I would like to generate a count for each change in i.e. the result expected in another column is 1,1,2,1,3.

    What formula would I have to write in the Power Query window to accomplish this?

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, June 02, 2014 4:02 AM

Answers

  • This is pretty complicated. You'd have to do something like this:

    1. Add an index column
    2. Group by the column whose count you're interested in, adding a new column "Values" for "All Rows"
    3. The next step has to be done in the "Advanced Editor". For each of the grouped table values we defined in step 2, we have to soft using the original index column and then we have to add a new index column inside the sorted table. The sort is necessary because the grouping operation doesn't necessarily preserve the sort of the original values -- even though in most cases it will probably seem to. The resulting formula looks like this:
    Sorted = Table.TransformColumns(GroupedRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex")}})
    4. Now we can expand out the grouped values by clicking on the expand icon in the header.
    5. Now we resort the entire table again by the original Index column.
    6. Some minor cleanup: add one to the GroupIndex column to define your final "count" value and then hide both the Index and GroupIndex columns.

    A sample program which demonstrates this looks like the following:

    let
        Source = Table.FromRows({{"A"}, {"B"}, {"A"}, {"C"}, {"A"}}, {"Value"}),
        InsertedIndex = Table.AddIndexColumn(Source,"Index"),
        GroupedRows = Table.Group(InsertedIndex, {"Value"}, {{"Values", each _, type table}}),
        Sorted = Table.TransformColumns(GroupedRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex")}}),
        #"Expand Values" = Table.ExpandTableColumn(Sorted, "Values", {"Index", "GroupIndex"}, {"Index", "GroupIndex"}),
        SortedRows = Table.Sort(#"Expand Values",{{"Index", Order.Ascending}}),
        InsertedCustom = Table.AddColumn(SortedRows, "Count", each [GroupIndex] + 1),
        RemovedColumns = Table.RemoveColumns(InsertedCustom,{"Index", "GroupIndex"})
    in
        RemovedColumns

    Monday, June 02, 2014 4:49 PM
    Owner

All replies

  • This is pretty complicated. You'd have to do something like this:

    1. Add an index column
    2. Group by the column whose count you're interested in, adding a new column "Values" for "All Rows"
    3. The next step has to be done in the "Advanced Editor". For each of the grouped table values we defined in step 2, we have to soft using the original index column and then we have to add a new index column inside the sorted table. The sort is necessary because the grouping operation doesn't necessarily preserve the sort of the original values -- even though in most cases it will probably seem to. The resulting formula looks like this:
    Sorted = Table.TransformColumns(GroupedRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex")}})
    4. Now we can expand out the grouped values by clicking on the expand icon in the header.
    5. Now we resort the entire table again by the original Index column.
    6. Some minor cleanup: add one to the GroupIndex column to define your final "count" value and then hide both the Index and GroupIndex columns.

    A sample program which demonstrates this looks like the following:

    let
        Source = Table.FromRows({{"A"}, {"B"}, {"A"}, {"C"}, {"A"}}, {"Value"}),
        InsertedIndex = Table.AddIndexColumn(Source,"Index"),
        GroupedRows = Table.Group(InsertedIndex, {"Value"}, {{"Values", each _, type table}}),
        Sorted = Table.TransformColumns(GroupedRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex")}}),
        #"Expand Values" = Table.ExpandTableColumn(Sorted, "Values", {"Index", "GroupIndex"}, {"Index", "GroupIndex"}),
        SortedRows = Table.Sort(#"Expand Values",{{"Index", Order.Ascending}}),
        InsertedCustom = Table.AddColumn(SortedRows, "Count", each [GroupIndex] + 1),
        RemovedColumns = Table.RemoveColumns(InsertedCustom,{"Index", "GroupIndex"})
    in
        RemovedColumns

    Monday, June 02, 2014 4:49 PM
    Owner
  • Hi,

    Thank you for sharing.  As you aptly put it, this is complicated.  I need to better understand the M language to write such a formula.

    Thank you for your time.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Tuesday, June 03, 2014 12:31 AM
  • Super useful. thnx!
    Wednesday, March 16, 2016 11:58 PM