Forum Discussion
Power Query index that resets when one column changes
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 |
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
3 Replies
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 GroupedRowsYou're welcome.
