Answered by:
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 2, 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
 Proposed as answer by Curt Hagenlocher Monday, June 2, 2014 4:54 PM
 Marked as answer by Ben MartensMicrosoft employee Tuesday, June 3, 2014 4:40 PM
Monday, June 2, 2014 4:49 PM
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
 Proposed as answer by Curt Hagenlocher Monday, June 2, 2014 4:54 PM
 Marked as answer by Ben MartensMicrosoft employee Tuesday, June 3, 2014 4:40 PM
Monday, June 2, 2014 4:49 PM 
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 3, 2014 12:31 AM 
Super useful. thnx!Wednesday, March 16, 2016 11:58 PM

Amazing. This works perfectly thank youThursday, July 5, 2018 6:29 PM

Many Many Thanks Curt... it worked perfectlyTuesday, December 18, 2018 1:14 PM

Hi,
you can skip step 6 if you already add the counting from 1 in step 3.
Just use
Sorted = Table.TransformColumns(GroupedRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex", 1, 1)}}),
Sunday, February 16, 2020 10:58 AM