RowNumber with Group by

(0) Share

Report

Posted on by douglas_filipe

Hi all!

I have a Sharepoint list:

RECRUITER	Position CODE
Lisa	P1
Homer	P2
Bart	P3
Homer	P4
Lisa	P5
Bart	P6
Homer	P7

RECRUITER is a person column.

I need to numerate the rows in a new column grouping by RECRUITER (person column) to get this result:

RECRUITER	Position CODE	MyPositions
Lisa	P1	1
Homer	P2	1
Bart	P3	1
Lisa	P4	2
Lisa	P5	3
Bart	P6	2
Homer	P7	3

I'm facing two problems, person column to group by and how number the rows.

Anyone can help me ?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Garima_PowerPlatform 170 on at

Like (1)

Report
Copy link

Link copied!
Hi,

You have 2 options
1. Grouping by Person Column

SharePoint's Person or Group column stores metadata (like Display Name, Email, etc.), so you must use the unique identifier (e.g., email or display name) to group rows.

In Power Automate, use Email or DisplayName property.

In Power Apps, refer to RECRUITER.Email or RECRUITER.DisplayName.

2. Numbering Rows

In Power Automate, use a counter to assign sequential numbers within the group.

In Power Apps, calculate the row number dynamically using CountIf().

Option 1: Using Power Automate

You can automate this process with Power Automate by creating a flow that numbers rows grouped by the RECRUITER column.

Create a New Column
In your SharePoint list, create a new column named Row Number (Type: Number). This column will hold the numbering values.

Build the Flow

Open Power Automate and create an Automated Cloud Flow triggered when an item is created or modified.

Use the Get Items action to retrieve all rows from the SharePoint list.

Use the Filter Array action to filter items based on the RECRUITER column (use dynamic content to match).

Add an Apply to Each action to iterate through the filtered items and number them.

Inside the loop, use the Update Item action to update the Row Number column based on the current loop index.

Configure the Grouping

Group rows in the Filter Array action using the RECRUITER column.

Use an expression in Power Automate to calculate the sequence number for each item.

Option 2: Using Power Apps

If you're building a Power Apps app that displays or interacts with your list, you can calculate and display row numbers dynamically.

Retrieve Data from the SharePoint List
Load the data from your SharePoint list into a collection using the ClearCollect function.

ClearCollect(
RecruiterData,
AddColumns(
SortByColumns('Your SharePoint List', "RECRUITER"),
"RowNumber",
CountIf(
'Your SharePoint List',
RECRUITER = ThisRecord.RECRUITER && ID <= ThisRecord.ID
)
)
)
Add a Gallery to Display Data
Bind the gallery's data source to the RecruiterData collection.
Use the RowNumber column to display the row numbering grouped by RECRUITER.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.

Was this reply helpful? Yes No
douglas_filipe 19 on at

Like (0)

Report
Copy link

Link copied!

Use PA isn't a possibility.

The second way not work, all rows get the same number.

Was this reply helpful? Yes No
AndrianaO 213 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi! Can you please explain in details how should the grouping work? You've provided an example, but I cannot see why first three lines with the different Recruiter names (Lisa, Homer, Bart) got the same MyPosition = 1.

Was this reply helpful? Yes No
WarrenBelz 154,447 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @douglas_filipe

I also have no idea where the numbers in the last column come from, but if you want totals by recruiter

Ungroup( AddColumns( GroupBy( AddColumns( YourSPList, RecruiterGroup, Recruiter.DisplayName ), RecruiterGroup, Grouped ), MyPositions, CountRows(Grouped) ), Grouped )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No

douglas_filipe

19 on at

Like (1)

Report

Copy link

Link copied!

I need create a sequence for each Recruiter, this image could be more clear:

RECRUITER	POSITION CODE	MyPositions
Lisa	P1	1
Lisa	P4	2
Lisa	P5	3
Homer	P2	1
Homer	P7	2
Bart	P3	1
Bart	P6	2
Bart	P8	3

Was this reply helpful? Yes No

Suggested answer

AndrianaO 213 Moderator on at

Like (1)

Report
Copy link

Link copied!

@douglas_filipe thanks for the clarification! I have a formula for you, it hopefully should do the trick

ClearCollect(

finalCollection,

Ungroup(

ForAll(

GroupBy(

AddColumns(

'Test List',

RecruiterGroup,

'test person'.DisplayName

),

RecruiterGroup,

Grouped

) As tableGrouped,

ForAll(

Sequence(CountRows(tableGrouped.Grouped)),

Patch(

Last(

FirstN(

tableGrouped.Grouped,

Value

)

),

{MyPosition: Value}

)

)

),

Value

)

)

Where 'Test list' is a connected SharePoint list and 'test person' is a person field in that list. Let me know if this works!

Was this reply helpful? Yes No