# 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

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

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"}),
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 2, 2014 4:49 PM

### All replies

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

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"}),
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 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.

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 you
Thursday, July 5, 2018 6:29 PM
• Many Many Thanks Curt... it worked perfectly
Tuesday, December 18, 2018 1:14 PM
{"ft_lang_label":"__label__en","ft_lang_prob":0.7557666,"math_prob":0.73849595,"size":4404,"snap":"2019-51-2020-05","text_gpt3_token_len":1075,"char_repetition_ratio":0.15363637,"word_repetition_ratio":0.87397707,"special_character_ratio":0.25999093,"punctuation_ratio":0.20689656,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.96758354,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-12-05T22:37:45Z\",\"WARC-Record-ID\":\"<urn:uuid:97ca06d8-5f40-4c20-999a-5c11e627ec6d>\",\"Content-Length\":\"83505\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:67328aca-8c3c-47e9-a2c7-60bb17ebb800>\",\"WARC-Concurrent-To\":\"<urn:uuid:008bcec4-6744-4e92-ab97-43714e2500fb>\",\"WARC-IP-Address\":\"96.17.233.217\",\"WARC-Target-URI\":\"https://social.technet.microsoft.com/Forums/en-US/5a89b690-abbf-4c0d-a0a2-a01fdfe0e4eb/restart-numbering-for-each-change-in?forum=powerquery\",\"WARC-Payload-Digest\":\"sha1:PV5W2WWN2OXEKYK7HCJIBH42R27JYY4I\",\"WARC-Block-Digest\":\"sha1:X5BIOGLP5TLYHEIME3FD2AXF6EYLXGYG\",\"WARC-Identified-Payload-Type\":\"application/xhtml+xml\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-51/CC-MAIN-2019-51_segments_1575540482284.9_warc_CC-MAIN-20191205213531-20191206001531-00154.warc.gz\"}"}