Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
That worked perfectly, thanks so much!