cancel
Showing results for 
Search instead for 
Did you mean: 

Custom Connector in Power Automate/Microsoft Flow for Power BI Data Flow Refresh

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

customcon.1.PNG

2. General section:

customcon.2.PNG3. 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:

customcon.3.PNG4. 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:

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

customcon.5.PNG6. Now coming back to the custom connector part: Under Definition we need to do the following:customcon.6.PNG7. 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:

customcon.7.PNG

It should look like this:

customcon.7.1.PNG8. 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:

customcon.8.PNG

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:

customcon.9.PNG

 

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"   

}

customcon.10.PNG

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.

customcon.11.PNG4.Click on the ellipsis (…) next to refreshRequest and select Edit.

customcon.12.PNG

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

customcon.13.PNG

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

customcon.14.PNG

 

Author: Shinjini Ghosh

Reviewer: Mounika Narayana Reddy

Meet Our Blog Authors
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Cambridge UK Power Platform User Group Leader, Technical evangelist and speaker. Always says yes to coffee! #LetsGetCoffee
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor
  • I am building business processes and applications that are easy for users' to stick to, so they can follow and understand them. In overall I transform processes to be more reliable and effortless. I am a proud co-organizer of SharePoint Saturday Warsaw and active community member, blogger and international speaker.