Managing Power BI workspaces often involves adding and removing users, which can become tedious if done manually. Fortunately, Power Automate can streamline this process.
Scenario Overview
Suppose you manage a Power BI workspace with multiple members. Typically, you would add users manually using the “Add people or groups” option. While effective, this approach is inefficient when dealing with large numbers of users.
By leveraging Microsoft Forms, Power Automate and an Active Directory (AD) group, you can automate user requests, additions and removals seamlessly. If you have the necessary permissions to manage AD groups, Power Automate can handle this task for you.
Understanding Active Directory (AD) Groups
Active Directory (AD) groups come in two types:
- Distribution Groups: Primarily used for email distribution.
- Security Groups: Used to assign permissions to shared resources.
For this automation, we will use a Distribution Group named “PBI”, which has been granted access to the Power BI workspace. Any user added to this group automatically gains workspace access as a Contributor.
If your IT team manages AD groups, the following approach may not be applicable. However, leveraging AD groups remains an effective way to control access to your Power BI workspace.
If you have the ability to manage AD groups directly, here is one approach on how you can automate the process.
Step 1: Create Microsoft Forms for Requests
First, create a Microsoft Forms request form that allows users to:
- Initiate the request
- Select the Power BI workspace (mapped to AD group names as shown in the picture)
This ensures a structured and consistent data entry process.
Step 2: Create Flow to Add Users
This step is to get the information from the Microsoft Form. The user’s email is readily available. For organizational purposes, I pull that out into a “Compose” action. This action is optional.
Step 3: Get List of Current Members in AD Group
Before adding a user to an AD group, I want to verify if the user is already a member. Without this, the flow will generate an error if an existing user is added. The connector “Office 365 Groups” has the actions needed here. This is shown here.
What I want to do first is use the “List group members”. An important consideration is if you have multiple AD groups. Going back to my Forms request, I offer up different options, with the names coinciding with AD group names that I have. Using this approach, I can allow for different AD groups for this action. If I only had one AD group, such as one called “PBI” – I could select that group in the action.
However, if I have multiple groups, the “Enter custom value” can be used along with the dynamic Forms field.
I’ll use the “Enter custom value” option which allows me to add in a dynamic field. I can use the “Select the Power BI Group” dynamic field. Because I have named my choices in the form to match the names of AD groups, I can select the output of that. (Note: My form has two choices of “PBI Group 2” and “PBI Group 3” which I don’t have AD Groups set up yet)
The next actions are to determine if a user is already within the AD Group or not. This can be accomplished by filtering the list to the user and the using the length() function.
The final step is to use a condition action based on the output of the previous setup. If the user doesn’t currently exist (length = 0) then add the member to the group. If they do exist, terminate the flow.
And with that, a user can be automatically added to the AD Group.
Additional Considerations
When designing this automation, consider the following enhancements:
- Approval Workflow: Before adding a user to the AD group, implement an approval step using the Approvals connector in Power Automate. This ensures controlled access and prevents unauthorized additions.
- Request Tracking: Store request details in a SharePoint list for auditability and reporting. This can help track who requested access, when, and whether the request was approved or denied.
- Notifications: Send automated email or Teams notifications to requesters, approvers, and relevant stakeholders when requests are submitted, approved, or denied.
By automating user management with Power Automate, you can streamline access control, reduce manual effort, and enhance security while maintaining a structured approval and tracking process.
Leave a Reply