Custom Connector in Power Automate/Microsoft Flow …

Issue:

Recently, I came across an issue where a customer wanted to automate the dataflow refresh in Power BI, whenever there is an update in an Excel file stored in Teams/SharePoint. As of today, Power BI provides a connector to execute dataset refresh but none for dataflow refresh.

Resolution:

Here are the steps we have executed to create a custom connector in Flows to achieve this.

A. Azure AD APP registration:

The first step is to register an application(Web App) in Azure AD. Ref:- https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app .

Please keep the Client ID and Client secret ready. We will need it while creating the Custom Connector in Flows.

B. Creating the Connector in Flows:

1. Create the custom connector from Flows -> Data -> Create from Blank

2. General section:

3. In the security section, select the Authentication Type as ‘Oauth2’, Identity Provider as ‘Azure AD’. Put in the Client Secret and Client ID from the App registration in Azure AD and fill the remaining steps as follows:

4. Once you update the connector, you will get the Redirect URL, which you need to add in the Authentication Section of your App registration as follows:

5. Under API permissions , give all the necessary permissions (Choose Power BI service -> Select the APIs and select Delegate all permission and Grant admin consent)

6. Now coming back to the custom connector part: Under Definition we need to do the following:7. When we select Import here and choose the verb as POST and then the REST API for dataflow refresh, please ensure we are deleting the POST portion otherwise we will get 404 error . Please see the screenshot below. The highlighted part in red should be deleted, and then we should import:

It should look like this:

8. Now, we head over to the Test section and Select the account to the Connections section -> sign in. Put in your group (workspace ID) and Dataflow ID and test connection:

We should be getting a 200 response here. If the Test Connection fails, then the actual Flow will definitely fail.

The response should look like this:

 

Blockers :

When using this connector in any Flow, we may encounter 400 error with the below error message:

{

“error”: {

“code”: “BadRequest”,

“message”: “Bad Request”,

“details”: [

      {

“message”: “‘refreshRequest’ is a required parameter“,

“target”: “refreshRequest”

      }

    ]

  }

}

 

Steps to overcome this error:

We need to update the definition of the custom connector to include the ‘refreshRequest’ parameter, as indicated by this error. Please see below for the steps to update the definition of the connector :

1. Click on POST, add this to the URL field: https://api.powerbi.com/v1.0/myorg/groups/%7BgroupId%7D/dataflows/%7BdataflowId%7D/refreshes and paste this to the Body section:

{

              “refreshRequest”: “y”   

}

2. Click on Import and see next page of this document.

3. Scroll down in the same page and click on the ellipsis (…) next to body, select Edit.

4.Click on the ellipsis (…) next to refreshRequest and select Edit.

5.Type y in the Default value field and click on Update connector.

6.Create a new Microsoft Flow and test. In the custom connector action, you will now see that the ‘refreshRequest ‘parameter is displayed.

 

Author: Shinjini Ghosh

Reviewer: Mounika Narayana Reddy