We will create a custom connector in Flow that refreshes any dataset in PowerBI you want and you can use it with any other connector.
Bonus, we can use it for calling other PowerBI API actions as well.
I was trying to find a way to refresh a PowerBI dataset for a long time with Microsoft Flow, but I gave up due to I am not a developer and my knowledge about APIs is limited. Lately I was motivated by a post from Chris Webb and the twitter chat we had to investigate again.
Before we start I have to mention again that I am not a developer and might be better ways to achieve this result. Hopefully for some of you with API knowledge will be basis for you to develop a better one.
That said, let’s start:
First step: Register App for PowerBI
We need to register a new application that can be used to call Power BI APIs. I am not going to fully describe the process as it is easy and there are better posts, like Microsoft’s documentation. Page to register app is https://dev.powerbi.com/apps.
This is what looks like
- App Name: Enter your app name, can be anything
- App Type: Server-side Web app.
- Redirect Url: https://login.live.com/oauth20_desktop.srf
- Home Page url: If you have a website enter url or a random one. At least I did.
- Choose APIs to access : We only need “Read & Write All Datasets” for refreshing API but we might want to select all of them as later we can extend Flow API. We can modify access from Azure portal also in a later stage.
Now we “Register” the app and save “Client ID” and “Client Secret” that will show up, somewhere safe. We are going to use them later.
Second Step: Create Flow custom connector
*there are some limits for amount of custom connectors based on Flow Plans license
Navigate to Microsoft Flow and select custom connectors under gear icon on upper right corner
We select create one from blank….
and give a name…
Next screen requires some general information regarding the new PowerBI custom connector we are building.
We don’t upload an icon for our connector for now but we add a short description.
We just need the base PowerBI api url.
- Host: api.powerbi.com
Next we move forward to security tab.
This took me a lot of time reading documentation on Azure authentication ( which are all Dutch to me — (Sorry my Dutch friends but Greek I can understand) so I end up entering below values that worked for me:
- Authentication Type: Select OAuth 2.0
- Identity Provider: Azure Active Directory
- Client id: Enter the Client ID of the app, that we copied when we register the app in PowerBI ( actually app registration is in AAD )
- Client Secret: Enter the Client Secret of the app, that we copied when weregister the app in PowerBI
- Login URL: https://login.windows.net
- Tenant ID: Enter “common”.
- Resource URL: https://analysis.windows.net/powerbi/api
- Scope: Enter “openid”
- Redirect URL : This will be visible after we create the connector. We are going to use it later.
Now we move to Definitions tab:
Here we will define PowerBI dataset refresh API call.
We add a new “Action” and enter details needed
- Summary: Short text on what the action does ( this will show up on Flow Actions )
- Description: A short description of the action
- Operation ID: we use “RefreshDataset”
We will now define details of our specific API request that refresh datasets
Click on “import from sample”
This is open a new window.
We select “POST” as this is required by PowerBI API to refresh a dataset.
PowerBI API documentation is available here
We are going to use the PowerBI API url for refreshing a dataset in “My Workspace” :
You notice that we don’t actually used a powerbi dataset id as described and required in api call. Instead we add in curly brackets a variable name ( can be any name ). This is important as we will use this variable (DatasetID) to ask for the datasetkey in order to call the API. It will make sense later.
- Variable name in url has to be included in curly brackets
Select import button.
We are ready to Create the connector!! Hit that button on top and wait few seconds.
Go to security tab and copy the Redirect URL that is now populated.
Depending your location this url might be different. Save it somewhere
Third step: Finalising and Testing connector
We add Redirect URL from Flow connector to our web app we registered before. Sign in to Azure portal https://portal.azure.com.
Search Azure Active Directory -> App Registrations -> PowerBI APIs ( name of our app ). Under setting we find “Reply URLs” and we add the url from Flow.
We are heading back to Flow and our custom connector. Let’s try to test our connector….
We select to edit connector…
and navigate to test tab where we create a new connection. We enter our credentials in pop up window.
Enter “DatasetID” in operations box and test it..fingers crossed!!!
*That is why we used a variable name before to construct API url. Now we can enter any dataset we choose
You can find dataset key in PowerBI url when you navigate to schedule refresh in PowerBI service
If everything worked as expected then you must see response status is accepted and a green ok mark..
*Sometimes it might fail with error “404 resource not found”. Updating the connector, exit ( wait a couple of minutes ) and edit again it will fix the issue, assuming that API url & body are correct
Let’s check also if it worked in PowerBI
*Time shows 2 hours later due to not be in GTM time, but minutes & seconds are exact.
That was it. Now we have a Flow connector that we can use in any workflow to refresh a PowerBI dataset.
The amazing thing is that we can use this connector for all PowerBI API calls.
Let’s add ( same procedure as above ) a call to refresh a dataset in a group/app workspace.
I suggest to read PowerBI API documentation as the possibilities are amazing and endless.
For example we can use a PowerApp on a report for users to refresh the dataset on their own ( there is a refresh limit of 8 times per day per dataset for Pro users )
Imagine a PowerApp that can create a workspace , clone a dataset/report add parameters and at the end refresh it with a press of a button on your mobile. How cool can it be.
Plus ( haven’t yet tried ) all collaboration features of Flow should be available like share with others, add co owner etc.
Finally, let’s check how our connector looks in a Flow
and with more actions ( teaser )
Hope you enjoyed the process and trigger you to play more PowerBI API actions.