Inserting data into a PowerBI Dataset via Flow and the REST APIs

by Power BI Admin chass on ‎01-25-2017 12:28 PM - last edited on ‎01-25-2017 04:28 PM by Jessica Regular Visitor

Last week I was playing with the very cool Rest Getting Started Sample (written in C#). I morphed it into a big loop that deleted all the rows in the dataset, and sent a row with a random value from 1-10. The reason I did this was to try out the new Flow integration with Alerts so I could demo sending emails to groups of people as a result of an alert.

 

I tweeted about this demo, and had a bunch of private requests from people hoping for a sample of updating data in Power BI directly from PowerApps. This blog post will show you how to insert data into a Power BI dataset from Microsoft Flow.

 

(I will do another post on how wire up this Flow to a PowerApps Application.)

 

Registering your application

Just like the Getting Started Sample, we need to start by registering your application for Oauth access to PowerBI.  While you can do this in the Azure Portal, it is much easier in the Power BI Dev Portal.

 

1. Navigate to https://dev.powerbi.com/apps and fill out the application name. Specify the App Type as Server-side Web app.

 

image

 

2. Fill out the the redirect URL and Home Page. In this case I am using the following values :

image

 

3. Register the application

Once you set the permissions for the application, register it and make sure to save the Client ID and Client Secret!

image

 

 

Create a Custom API in Flow to all the datasets endpoint and add the rows to the PowerBI dataset

 

1. The first step is to log into https://Flow.microsoft.com. Click the gear icon and select the Custom APIs menu.

 

image

 

2.  Click Create custom API

…In this case just click the button.

 

image

 

3.  Set the properties of the Custom API.

I have attached the swagger file below.  This one was hand created by Sunay Vaishnav, but he is working on making this much easier!

 

image

 

4. Set the security properties.

In this case, as the swagger file already specifies Oauth and Azure Active Directory, all I needed to do was set the following and save the custom API.

 

image

 

Create a Flow to use the Custom API

1. Click on My Flows > Create from blank

image

 

2. Set the trigger to Recurrence, and Actions to the custom API name you gave it above.

image

 

3.  Sign into Power BI to set the properties of the custom API.

image

 

(If you don’t get this sign in dialog you probably registered the application incorrectly in step 1.)

image

 

4.  Set the properties of the Action.

If everything was done correctly, the Dataset and Table name should auto-populate to drop-down menus so you can see all the datasets and tables.  Note that the dataset you are updating MUST HAVE BEEN CREATED WITH THE REST APIs INITIALLY!  If this limitation is something you would like changed please vote on this idea:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9366600-update-rows-rest-api

 

 

image

 

Here's the swagger file you will need to do this:

https://msdnshared.blob.core.windows.net/media/2017/01/PBISwagger.json_.txt 

 

Remember to rename this to a *.JSON file name

Comments
by yoshihirok Member
‎01-25-2017 02:36 PM - edited ‎01-25-2017 05:44 PM

Exciting, Thank you for detail step, and the Swagger file.

I will do this for non-coding automation with Power BI and Microsoft Flow.

Each non-programmer can create real-time reports.

-- I hope the step-by-step for creat the sample dataset to Power BI Service.
-- for share this post to my friends/parterns.
-- Now, I need to learn other documents.