Creating a rank/index column based on conditions in Power Query

Question

Hi all,&nbsp;I'm trying to add a column in Power Query that will increment based on data in other columns.&nbsp; The data contains events, event attendees and questions.&nbsp; I am trying to replicate the QuestionNumber column per below sample data.&nbsp; For each event, the question number needs to start at 1 and within that event, if the questionid changes the question number needs to increment then restart at 1 for the next event.&nbsp;Sample of data:eventidprioreventidpollquestionidpriorquestionideventuseridQuestionNumber2983871null34137753null44054008113134968298387135624240341377534396881531313496831349683562424035624240436378494131349683134968356242403562424043652207813134968313496835624240356242404376762121313496831349683562424035624240437558930131349683134968356242403562424043754714713134968313496835624240356242404362053801313496831349683562424035624240437314536131349683134968356250433562424043968815323134968313496835625043356250434363784942313496831349683562504335625043436522078231349683134968356250433562504343755893023134968313496835625043356250434375471472313496831349683562504335625043437446242231349683134968356250433562504343731453623134968313496835625151356250434396881533313496831349683562515135625151436378494331349683134968356251513562515143652207833134968313496835625151356251514375471473313496831349683562515135625151437314536331349683134968356251513562515143664780432602307313496829974050356252174373201801&nbsp;The formula I am using in Excel to calculate the QuestionNumber column is:&nbsp;=IF(A2&lt;&gt;B2,1,IF(AND(A2=B2,C2=D2),F1,F1+1)).&nbsp;Any advice on the best way to accomplish this would be appreciated.&nbsp;Thanks!

jakinta · Accepted Answer

I hope i understood you well 🙂Try this in new query and adjust accordingly.&nbsp;let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndRLDsIwDATQu2TNwp9x7Jyl4gaIHfcniE9bFIHTVUdRn9J4lC5LkRYazuVUrrfLpT8UrO6m6wpABqLgcj4tRVnRavT1lapVgYD2HNrfC+557zZp49YIreqBhnlnIuQx7bx6FZZ5ZxZN6YCDM3z+fEKmcWA/ZZjWn84I+u0y/W3cK+b6G7pEfyOX6WHs/vcwdMBjSvMu0wMbf7tUDxv3jMkeRi7Tw8Cl5jlyk3P5fGeFBz3PJ5VEyXdOWvP+83rfBxP2135C/LhH5zs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [eventid = _t, prioreventid = _t, pollquestionid = _t, priorquestionid = _t, eventuserid = _t]),
    Grouped = Table.Group(Source, {"eventid", "pollquestionid"}, {{"Gr", each _, type table}}),
    Grouped2 = Table.Group(Grouped, {"eventid"}, {{"Gr2", each _, type table }}),
    AddIndex = Table.AddColumn(Grouped2, "Custom", each Table.AddIndexColumn([Gr2], "Question Number",1,1)),
    Expanded2 = Table.ExpandTableColumn(AddIndex, "Custom", {"Gr", "Question Number"}, {"Gr", "Question Number"}),
    Expanded = Table.ExpandTableColumn(Expanded2, "Gr", {"prioreventid", "pollquestionid", "priorquestionid", "eventuserid"}, {"prioreventid", "pollquestionid", "priorquestionid", "eventuserid"}),
    FINAL = Table.RemoveColumns(Expanded,{"Gr2"})
in
    FINAL&nbsp;&nbsp;

Forum Discussion

Creating a rank/index column based on conditions in Power Query

2 Replies

Related content

RANK indexing with condition

Rank by index

Index / Rank

Calculated column for rank/index

Moving Average by Ranked Index

eventid	prioreventid	pollquestionid	priorquestionid	eventuserid	QuestionNumber
2983871	null	34137753	null	440540081	1
3134968	2983871	35624240	34137753	439688153	1
3134968	3134968	35624240	35624240	436378494	1
3134968	3134968	35624240	35624240	436522078	1
3134968	3134968	35624240	35624240	437676212	1
3134968	3134968	35624240	35624240	437558930	1
3134968	3134968	35624240	35624240	437547147	1
3134968	3134968	35624240	35624240	436205380	1
3134968	3134968	35624240	35624240	437314536	1
3134968	3134968	35625043	35624240	439688153	2
3134968	3134968	35625043	35625043	436378494	2
3134968	3134968	35625043	35625043	436522078	2
3134968	3134968	35625043	35625043	437558930	2
3134968	3134968	35625043	35625043	437547147	2
3134968	3134968	35625043	35625043	437446242	2
3134968	3134968	35625043	35625043	437314536	2
3134968	3134968	35625151	35625043	439688153	3
3134968	3134968	35625151	35625151	436378494	3
3134968	3134968	35625151	35625151	436522078	3
3134968	3134968	35625151	35625151	437547147	3
3134968	3134968	35625151	35625151	437314536	3
3134968	3134968	35625151	35625151	436647804	3
2602307	3134968	29974050	35625217	437320180	1