Working within a School Division, we have a very transient workforce. Staff often change Schools, Grades or even leave. We use Power BI extensively for reporting on Student Performance however, I want to ensure that only the relevant people see the relevant students. Power BI does not natively have the ability to connect to Azure AD where I can check each time I refresh our data that data compliance is being met.
In this article, I'll show you how I use Microsoft Graph API and Power Automate to maintain data compliance with Row Level Security in Power BI.
Create the Microsoft Graph API App Registration
First we need to register an App within Azure AD. Navigate to portal.azure.com and sign in with your Organizational Account.
Select “Azure Active Directory” blade on the left then, select the "App registrations” blade.
Click "New registration" at the top of the page. We are now going to set the fields for the new App.
Name: This can be set to your requirements
Supported Account Type: Accounts in this organizational directory
Redirect URI: Web: https://login.windows.net
Click the "Register" button located at bottom of screen to create your App Registration.
We should now be show the App Registration Successful Page where we can set the rest of the values and permissions we will require. Copy the Application ID at the top of the page as, will will be using this as our Client ID within Power Automate when we create our Custom Connector. Click "New registration' at the top of the page. We are now going to set the fields for the new App. Click "Certificates & secrets" at the left of the page.
Click "New client secret" at the bottom of the page. We are now going to set the fields for the new App.
Description: This can be set to your requirements
Expires: Set to your requirements
Click the "Add" button located at bottom of the pop up window to create your App Secret. The Client secret will be generated and shown with both its Value and ID. Copy the Secret Value as, will will be using this as our Client Secret within Power Automate when we create our Custom Connector.
We now need to add the API Permissions to get User Data from Microsoft Graph. From the App Registration Page, click the "View API permissions" button located at the bottom of the screen. This will open the "Configured permissions" screen.
There should already be the one Permission for Microsoft Graph Assigned to the App Registration however, we need to add a couple more to be able to get the User Details back that we are going to use within Power BI. Click "Add a permission".
Click "Microsoft Graph" at the top of the page.
Click "Delegated Permissions" at the top of the page.
This will now open all the Microsoft Graph API Permissions that are available for you to use. Scroll to the very bottom of the list and expand "User". Select the following permissions:
User. Export.All
User.Read
User.Read.All
User.ReadBasic.All
Click the "Add permissions" button located at the bottom of the screen. The Microsoft Graph API Permissions will now be added to the App Registration. Click "Grant Admin Consent" above the listed Permissions to allow App Users to utilise these permissions.
Leave the App Registration Details page open as, we will be copying the Redirect URL from Custom Connector within Power Automate.
Create the Custom Connector within Power Automate
We now need to create our new Power Automation Custom Connector. Navigate to Power Automate from the Waffle Menu in Office 365. Click Date > Custom Connectors > New Custom connector > Create from blank. Provide a Connector name, i.e., “Microsoft Graph API”, and click “Continue”.
We are now going to set the fields for the new Connector.
Icon: You can upload a custom icon to use
Icon Background Colour: Hex Value Colour to use as background colour
Description: This can be set to your requirements
Scheme: HTTPS
Host: graph.microsoft.com
Base URL: leave default
Click the "Security" button located at bottom of the pop up window.
We are now going to set the Security fields for the new Connector.
Authentication Type: OAuth 2.0
Identity provider: Azure Active Directory
Client ID: Copied from registered application
Client Secret: Copied from registered application
Login URL: leave default
Tenant ID: leave default
Resource URL: https://graph.microsoft.com
Click the "Create Connector" button located at top of the window without defining any Actions or Triggers. The Connector will now be created and, the Redirect URL at the bottom of the page will be populated. Copy this URL.
Return to your App Registration Page in Azure, click the "Redirect URls" link located at the top of the screen. This will open the "Redirect URLs" screen. Click "Add URI" under the Web Container and paste in the Redirect URL from your Custom Connector. Click "Save" at the top of the page.
Return to your Custom Connector within Power Automate. Now, that the Custom connector has been created, we need to test it. Select “Test” tab. Click “New connection”. This should now bring up an Authentication Screen. Sign in and wait. If the App Registration in Azure has been successful, a connection will be created and listed within the selected connection box.
We now need to create our Action to call Microsoft Graph API and get our Users. Click on the “Definition” tab. Create an action, which will allow us to retrieve all Users in the organisation.
We are now going to set the fields for the new Action. Use descriptive names, as you can only have one action per Method+Path combination
Summary: Set to your requirements
Description: Set to your requirements
Operation ID: Set to your requirements
Click “Import from sample” in the Request section,
Select "GET" .This URL will get all Organization Users where the Job Title is not blank. We will utilize the $Top and $SkipToken parameters when we build our flow.
URL: https://graph.microsoft.com/v1.0/users?$Top=500&$Filter=jobTitle ge '!'&$Skiptoken
Click the "Import" button located at bottom of the pop up window. Flow will create query elements out of the parameters and trim the URL. Leave Response and Validation sections intact, and click “Update connector” located at the top of the page.
Now lets Test the operation. Select “Test” tab. Leave the $Top, $Filter and $Skiptoken parameters blank. Click the “Test Operation” button. If the Custom Connector has been created successful, you should see the data being returned in the Response section located at the bottom. We are now ready to build our Power Automation to use within Power BI. Click "Close" at the top of the page.
Building the Power Automation
We now need to create our new Power Automation Cloud Flow. Navigate to Power Automate from the Waffle Menu in Office 365. Click Create > Instant Cloud Flow. Provide a Flow name, i.e., “Get Organization Users”, select the “Manually Trigger a Flow” trigger and click “Create”. We will change this trigger later once we have built and tested the Automation.
I'm going to create six Initialize Variable actions. These will be used within the Automation for setting the URL Filters as well as collecting the Response Results
Name : “Do Until”
Type : “Integer”
Value : “0”
Name : “Filter”
Type : “String”
Name : “Skip Token”
Type : “String”
Name : “Next Link”
Type : “String”
Name : “Extract”
Type : “Array”
Name : “Users Array”
Type : “Array”
Next we are going to add a Do Until Action and Switch Action. Set the left side of the Do Until action to the Do Until variable, change the clause to is equal to and set the value as 1. Set the Switch Action to the Do Until variable and set the left Case value to 1.
We are going to work on the right hand side of the Switch Action first (Default). Add your Custom Connector Action that we created previously and set the Top and Filter Parameters
Top: 500
Type : jobTitle ge '!'
Next we are going to add a Parse JSON Action, a Compose Action and a Append to array variable Action.
We are now going to set the fields in the Actions that we have added. Set the fields
Parse JSON Action
Content: Body from the Get Organization Users Action
Schema : Select the Code Snippet Below
{
"type": "object",
"properties": {
"@@odata.context": {
"type": "string"
},
"@@odata.nextLink": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"businessPhones": {},
"displayName": {},
"givenName": {},
"jobTitle": {},
"mail": {},
"mobilePhone": {},
"officeLocation": {},
"preferredLanguage": {},
"surname": {},
"userPrincipalName": {},
"id": {}
},
"required": []
}
}
}
}Compose Action
Inputs: {"Items": [Body from the Parse JSON Action immediately above]}
Append to array variable Action
Name Users Arrays variable
Schema : Output from the Compose Action immediately above
We are going to work on the left hand side of the Switch Action now (Paging Available). Unfortunately, when there is Paging from a HTTP Action, you can not reuse your Custom Connector so we have to use the HTTP Action. We are going to add a HTTP Action, Parse JSON Action, a Compose Action and a Append to array variable Action. The Field settings in these are almost identical to those we have set on the Default case.
We are now going to set the fields in the Actions that we have added. Set the fields
HTTP Action (click the show advanced options link)
Method: GET
URL : Next Link variable
Authentication : Azure Directory OAuth
Tenant : Tennant ID obtained from Azure Active Directory
Audience : https://graph.microsoft.com/
Client ID : Copied from registered application
Credential Type : Secret
Secret : Copied from registered application
Parse JSON Action
Content: Body from the HTTP Action
Schema : Code Snippet used above
Compose Action
Inputs: {"Items": [Body from the Parse JSON Action immediately above]}
Append to array variable Action
Name Users Arrays variable
Schema : Output from the Compose Action immediately above
Now we have our Cases defined, we need to check if the Graph API has Results Paging. Below the Cases, add two Set variable actions. We are now going to set the fields in the Actions that we have added.
1st Set Variable Action
Name: Next Link
URL : Select the Expressions Tab rather than Dynamic Content to build the expression. You may need to change the Output Names dependant on what you called your Actions.
if(equals(variables('Do Until'),0),outputs('Get_Organization_Users')?['body']['@odata.nextLink'],outputs('Get_Organization_Users_-_Skip_Token')?['body']['@odata.nextLink'])2nd Set Variable Action
Name: Next Link
URL : Stop
The second variable action also requires to have its "Configure run after" set. Select "has failed" and "is skipped" and click done.
Now we have our Next Link defined, we can finish off the variables we need to make the HTTP Call if there is paging within the Custom Connector Call. Next we are going to add a Find text position Action, a Compose Action and two Set variable Actions. We are now going to set the fields in the Actions that we have added.
Find Text Position Action
Text: Next Link
Search Text : v1.0
This action also requires to have its "Configure run after" set. Select "is successful" and "is skipped" and click done.
Compose Action
Inputs: Select the Expressions Tab rather than Dynamic Content to build the expression. You may need to change the Output Names dependant on what you called your Actions.
sub(int(length(variables('Next Link'))),int(outputs('Find_text_position')?['body']))1st Set Variable Action
Name: Skip Token
URL : Select the Expressions Tab rather than Dynamic Content to build the expression. You may need to change the Output Names dependant on what you called your Actions.
if(equals(variables('Next Link'),'Stop'),'',substring(variables('Next Link'),add(indexOf(variables('Next Link'),'v1.0'),5),sub(outputs('String_Length'),10)))2nd Set Variable Action
Name: Do Until
URL : Select the Expressions Tab rather than Dynamic Content to build the expression. You may need to change the Output Names dependant on what you called your Actions.
if(equals(variables('Next Link'),'Stop'),0,1)Lets save the Automation and Test it.
My test was successful. I have 8 calls to Microsoft Graph. 1 used my Custom Connector and 7 used the HTTP Request with the Paging. Now we need to make this Automation accessible to Power BI. Lets Edit it. We want to delete the “Manually Trigger a Flow” trigger action and replace it with "When a HTTP Request is received". You will get a warning about removing the trigger however, we have not referred to the trigger within the Automation so we are save to remove it. Save the Automation again and the HTTP Post URL will be created for you. Take a copy of this URL as we need it for Power BI.
We need to edit the Automation for one last time to provide Power BI the Array we have compiled from the Microsoft Graph API results. Add a Response Action after the Do Until Block. Set the Body to the Users Array variable.
Building the Power BI Model
Now you have the Automation built, you can access this data source either in Power BI Desktop or within a Power BI Dataflow. Lets start by opening Power BI Desktop. Click Get Data > Blank Query. Open the Blank query in Advanced view and paste the Code Snippet below in. You will need to change the [HTTP Post URL] Block below to reflect the URL that was created within your Automation.
let
url = "[HTTP Post URL]",
#"HTTP Response Body" = "{}",
#"Parse Response Body" = Json.Document(#"HTTP Response Body"),
#"Build Quey String" = Uri.BuildQueryString(#"Parse Response Body"),
#"Power Automate" = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(#"Build Quey String") ] )),
#"Convert to List" = Table.FromList(#"Power Automate", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expand List Items" = Table.ExpandRecordColumn(#"Convert to List", "Column1", {"Items"}, {"Items"}),
#"Expand List Value Items" = Table.ExpandRecordColumn(#"Expand List Items", "Items", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expand List Value Items", "value"),
#"Get Values" = Table.ExpandRecordColumn(#"Expanded value", "value", {"businessPhones", "displayName", "givenName", "jobTitle", "mail", "mobilePhone", "officeLocation", "preferredLanguage", "surname", "userPrincipalName", "id"}, {"businessPhones", "displayName", "givenName", "jobTitle", "mail", "mobilePhone", "officeLocation", "preferredLanguage", "surname", "userPrincipalName", "id"}),
#"Expanded businessPhones" = Table.ExpandListColumn(#"Get Values", "businessPhones"),
#"Renamed columns" = Table.RenameColumns(#"Expanded businessPhones", {{"businessPhones", "Work #"}, {"displayName", "Display Name"}, {"givenName", "First Name"}, {"jobTitle", "Job Title"}, {"mail", "Email"}, {"mobilePhone", "Cell #"}, {"officeLocation", "School or Office"}, {"surname", "Surname"}, {"userPrincipalName", "UPN"}, {"id", "User ID"}}),
#"Sorted rows" = Table.Sort(#"Renamed columns", {{"Surname", Order.Ascending}}),
#"Transform columns" = Table.TransformColumnTypes(#"Sorted rows", {{"Work #", type text}, {"Display Name", type text}, {"First Name", type text}, {"Job Title", type text}, {"Email", type text}, {"Cell #", type text}, {"School or Office", type text}, {"Surname", type text}, {"UPN", type text}, {"User ID", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Work #", null}, {"Display Name", null}, {"First Name", null}, {"Job Title", null}, {"Email", null}, {"Cell #", null}, {"School or Office", null}, {"Surname", null}, {"UPN", null}, {"User ID", null}}),
#"Transform columns 1" = Table.TransformColumnTypes(#"Replace errors", {{"preferredLanguage", type text}}),
#"Replace errors 1" = Table.ReplaceErrorValues(#"Transform columns 1", {{"preferredLanguage", null}})
in
#"Replace errors 1"Let's Close and Apply the Query. Your data should now be loaded into your Power BI Model. If you check your Automation you created, you will see the Runs as the data is being queried.
Now we can create a Measure within Power BI that can be utilized within Row Level Security.
Signed in User =
CALCULATE(FIRSTNONBLANK('Users'[Job Title],1),'Users'[UPN]=USERPRINCIPALNAME())Click Modeling > Manage Roles > Create. On the Table you want Row Level Security Applied to add the Table FIlter Expression.
Let us publish the Report to the Power Bi Service and make changes to the Setting to ensure that we get refreshes within the service. Navigate to Power BI from the Waffle Menu in Office 365. Select the Workspace you published your report to.
Click Model > Content Menu > Settings.
Set the fields
Data Source Credentials
Authentication Method: Anonymous
Privacy Level : Organization
Skip test Connection: True
Sign in and the Data Source Credentials will be updated. Now refresh your data model and you should get no errors.
If you are using RLS remember to assign Users or Groups Access within the Role.
Some Final Thoughts
I have used this method of getting information from Microsoft Graph API not only for Row Level Security but, to expose additional service data such as Microsoft Bookings and MS Teams Activity within my Power BI Reports.
Additionally, I am now using this method within Power App Data Flows to populate Dataverse entities.
ความคิดเห็น