Power Query index that resets when one column changes

[ErinKlomp]

Hi everyone,

I am a first time user of Power Query and am stuck. I have no experience with similar software.

I want to create an index, which resets to 1 when one column changes value. As an example, this is some of my data. I added the pink row to show what I want to accomplish. I just don't get it. I prefer not to use the advanced editor as I do not understand it (but if someone can give me a foolproof step-by-step, I can try). I have tried many things, but my index keeps ending up to continue counting.

If someone can help me explain how to achieve this, I would greatly appreciate this!

evaluatieperiodeid	respondentid	Index
59141	257262	1
59141	257263	2
59141	257266	3
59141	257268	4
59142	256898	1
59142	256899	2
59142	256903	3
59142	256904	4

[ErinKlomp]

@dufoq3 Thank you so much!!!! It worked perfectly!

[dufoq3]

You're welcome.

[dufoq3 · Accepted solution]

Hi @ErinKlomp,

Group Rows:

Change the code of Grouped rows where you replace _ with Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type) and remove RED part of that code.

= Table.Group(Source, {"evaluatieperiodeid"}, {{"All", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}})

Expand columns you want (unthick Use original column name as prefix)

You can find whole code with sample data included here (you have to replace whole code with this one via Advanced Editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrU0NDFU0lEyMjU3MjNSitVBEzLGFDLDFLKACxmBhcwsLLEIWaILWRoYYwqZKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [evaluatieperiodeid = _t, respondentid = _t]),
    GroupedRows = Table.Group(Source, {"evaluatieperiodeid"}, {{"All", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}})
in
    GroupedRows