Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sjwshea
Frequent Visitor

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

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:

eventidprioreventidpollquestionidpriorquestionideventuseridQuestionNumber
2983871null34137753null4405400811
3134968298387135624240341377534396881531
3134968313496835624240356242404363784941
3134968313496835624240356242404365220781
3134968313496835624240356242404376762121
3134968313496835624240356242404375589301
3134968313496835624240356242404375471471
3134968313496835624240356242404362053801
3134968313496835624240356242404373145361
3134968313496835625043356242404396881532
3134968313496835625043356250434363784942
3134968313496835625043356250434365220782
3134968313496835625043356250434375589302
3134968313496835625043356250434375471472
3134968313496835625043356250434374462422
3134968313496835625043356250434373145362
3134968313496835625151356250434396881533
3134968313496835625151356251514363784943
3134968313496835625151356251514365220783
3134968313496835625151356251514375471473
3134968313496835625151356251514373145363
3134968313496835625151356251514366478043
2602307313496829974050356252174373201801

 

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!

1 ACCEPTED SOLUTION
Jakinta
Solution Sage
Solution Sage

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

 

 

View solution in original post

2 REPLIES 2
Jakinta
Solution Sage
Solution Sage

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors