Row Level Security in Power BI Dataset using Power Automate and Microsoft Graph

Updated: Apr 10

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.

Microsoft Graph API Registration
Register Application

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.


App Registration Details

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.

Client Secret

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.

API Permissions

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".

Graph API Permissions

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”.

Create Custom Connector

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.





Custom Connector Security

We are now going to set the Security fields for the new Connector.

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.

Update App Registration

Custom Connector Test

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.

Custom Connector Definitions

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.

Test Operations

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.

Create Power 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

Declare Variables
  • 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.

Do Until
Switch Statement

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.


Parse Actions

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.

Parse Actions

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.

Next Link

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.

Next Link

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.

Testing Automation

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.

HTTP Request

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.


HTTP Response

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.

Power Query

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.

RLS Roles

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.

Configure Settings
  • 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.

Related Posts

See All