Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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 |
Solved! Go to 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
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