Answered

Create a gallery with all our departments with the number of employees in each department, however, we have more than 999 employees

(1) Share

Report

Posted on by NFox

I’m looking to create a list of all the users in our company (roughly 1200 users) and display a grouped list of all the departments with the count of users in each department (see below). I managed to create this, but because I’m using the searchusersV2 function, it only displays the first 999 employees.

Sort(
 GroupBy(
 Filter(Office365Users.SearchUserV2({isSearchTermRequired: "No", top:999}).value,Len(Department)>0)
 ,"Department","Grouped")
,Department)

To get around the 999 max users I tried the following with no luck:

SearchUsersV2 function as shown above
- Issue: Limits to 999 users
AzureAD.GetGroupMembers formula
- Issue: doesn’t retrieve department info
I tried combining the 3 & 4 using addcollumns and the lookup function, but it didn’t return all users' department information

Sort(AddColumns(
AzureAD.GetGroupMembers("81d80d8b-e7af-4aad-8752-ab5546bf7b3f",{'$top': 999}).value,
"User Name",displayName,
"Department Details",
LookUp(
Office365Users.SearchUserV2({isSearchTermRequired:"No", top:999}).value,mail = Mail,Department)
),displayName)

4. Export to a SharePoint list via Power Automate.

This works however, I am not sure how to edit the list as new users are added or removed. Or alternatively, delete the whole list and reupload it every week?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@NFox

What happens if you remove top:999 ?

Was this reply helpful? Yes No
NFox 8 on at

Like (0)

Report
Copy link

Link copied!

@poweractivate I get the same results

Was this reply helpful? Yes No
Verified answer

poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

@NFox

I checked it further, and I also found an existing explanation that is very effective, in-depth, and explains the issue you ran into. According to @Pstork1 in this thread Office365Users.SearchUserV2 returning only 999 users
There is a hard coded max of 999 results with that connector.

You may refer to this post by @v-xiaochen-msft that I like as a starting point example, to try and implement a custom solution involving pagination to get 999 users at a time, and eventually get all 1200 users, or however many you may have, here's the thread to use as a starting point:
GET ALL USER FORM OFFICE 365

Check if the above helps @NFox

Was this reply helpful? Yes No
Pstork1 69,022 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Given the limitations in Power Apps I would invoke a Power Automate flow to get this data, calculate the number of employees per department, and return the data set to Power Apps for display. It should provide better performance than trying to load it all locally and do all the calculations.

Was this reply helpful? Yes No
brazec1 2 on at

Like (0)

Report
Copy link

Link copied!

How did you display the Users per Department?

@NFox

Was this reply helpful? Yes No