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

[sjwshea]

Hi all,

I'm trying to add a column in Power Query that will increment based on data in other columns.  The data contains events, event attendees and questions.  I am trying to replicate the QuestionNumber column per below sample data.  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.

Sample of data:

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

The formula I am using in Excel to calculate the QuestionNumber column is: =IF(A2<>B2,1,IF(AND(A2=B2,C2=D2),F1,F1+1)).

Any advice on the best way to accomplish this would be appreciated.

Thanks!

[Jakinta · Accepted solution]

I hope i understood you well 🙂

Try this in new query and adjust accordingly.

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

[sjwshea]

That worked perfectly, thanks so much!