Skip to main content

Introducing the Power BI APIs for Preview

Headshot of article author Josh Caplan

What it means to be a developer has dramatically changed and evolved in recent years. Gone are the days of disconnected single platform software that can take months and years to develop before anyone even uses it. We are now in the age of connected applications that span all of our devices and services. The Power BI APIs are the first step to allow Power BI developers to integrate Power BI into this connected world.

When we set out to design a modern developer experience for Power BI, we looked at what other major web platforms were doing and we saw lots of consistency. In these services, the majority of UI functionality is exposed to developers through REST APIs. Applications integrate their authentication with the service using OAuth 2.0. By following some of these same principles, we hope that you can build applications that extend Power BI in ways we never thought of, and allows you to extend your own applications with Power BI.

We have kicked off our developer experience with an initial round of APIs for preview. Let’s look at some functionality that is available. For reference, I will show an example of an application that I built which extends Google Chrome into Power BI. I created a Chrome extension that can take data inside a web page and send it to Power BI to be analyzed. Before we actually look at any APIs, let’s talk about authentication. Power BI uses OAuth 2.0 to authenticate its users. We have extended that same functionality to our developers so that applications can act on behalf of any Power BI user.

The extension shows up in Chrome and has a link for you to sign in to Power BI. When you click that link, I invoke the Power BI login flow. Information on how to invoke login, can be found here. Power BI uses Azure Active Directory (AAD) for OAuth and AAD asks you to sign in with your company user name and password. Once the you log in, you are prompted to approve certain permissions for your application. I chose the permissions that I wished to ask for when I registered my application in AAD.

These permissions are what allow your application to sign in on behalf of a user and perform certain actions. The below table gives a description of each of these permissions:

Display Name Description
Add data to any of your datasets in Power BI (preview) Gives an app access to add or delete a user’s dataset rows. This permission does not grant the app access to the user’s data.
Create content (preview) App can automatically create content and datasets for a user.
View content properties (preview) The app can view object properties owned by the user including content and dataset names as well as dataset schema. This permission does not grant the app any access to the user’s data.

 

Once you consent to the permissions, an access token is returned to the app that is used to call the APIs on your behalf. So now let’s talk APIs. Below are the APIs that are now available for Preview:

You can try all of these APIs out without writing a single line of code using  https://docs.powerbi.apiary.io.

GET Datasets – Lists all the datasets that the signed in user has including their names and IDs.

POST Datasets – Creates a new dataset based on a JSON schema definition passed in the request body. This schema can contain multiple tables.

POST Table/Rows – Appends rows to the specified table in a dataset. Any dashboards that use that dataset will have their tiles automatically updated to reflect the new data.

DELETE Table/Rows – Clears all the rows in a table.

While these are just the first APIs available, you can still do some powerful things with them. Finishing up my example of the Chrome extensions, the Power BI user is now logged in. You can go find any table of data in a web page, highlight it and click “Analyze in Power BI”.

Next, in my code I analyze the schema of the table that the you selected and I generate the JSON schema of a new dataset:

{   "Name":"wunderground_5637",   "Tables":[      {         "Name":"wunderground_5637",         "Columns":[            {               "name":"Time",               "DataType":"string"            },            {               "name":"Temp",               "DataType":"string"            },            {               "name":"Feels Like",               "DataType":"string"            },            {               "name":"Dew Point",               "DataType":"string"            },            {               "name":"Humidity",               "DataType":"string"            },            {               "name":"Conditions",               "DataType":"string"            },            {               "name":"Precip",               "DataType":"string"            },            {               "name":"Hourly Precip",               "DataType":"string"            },            {               "name":"Wind",               "DataType":"string"            },            {               "name":"Cloud Cover",               "DataType":"string"            },            {               "name":"Pressure",               "DataType":"string"            }         ]      }   ]}

 

Then I make a REST call to POST Datasets to create this new dataset. The app passes the access token it got when the user signed in. This ensures that the dataset gets created on the correct users Power BI account.

 

POST https://api.powerbi.com/beta/myorg/datasets

Content-Type: application/json

Authorization: Bearer eyJ0-XXXXXXX-XXXXXX-XXXXXXX-UHPA

 

Request Body:

{  "Name": "wunderground_5637",  "Tables": [    {      "Name": "wunderground_5637",      "Columns": [        {          "name": "Time",          "DataType": "string"….

 

The response to this call is a 201 create and the name and ID of the created datasets is returned:

content-type: application/json; charset=utf-8

location: https://api.powerbi.com/beta/myorg/datasets/2e8b21e6-7015-4d11-9d6b-6166990400a5

 

Response Body:

{  "id": "2e8b21e6-7015-4d11-9d6b-6166990400a5",  "name": "wunderground_5637"}

 

The newly created dataset now shows in the UI and can be explored:

 

That takes care of creating the dataset, but we also need to add data to it. We do this by making another API call and passing the JSON for the rows.

 

POST https://api.powerBI.com/beta/myorg/datasets/2e8b21e6-7015-4d11-9d6b-6166990400a5/tables/wunderground_5637/rows

 

Content-Type: application/json

Authorization: Bearer eyJ0-XXXXXXX-XXXXXX-XXXXXXX-5Z_g

 

Request Body:

 

 {  "rows": [    {      "Time": "12:00 pm",      "Temp": "34 °F",      "Feels Like": "25 °F",      "Dew Point": "21 °F",      "Humidity": "59%",      "Conditions": "",      "Precip": "Clear",      "Hourly Precip": "1%",      "Wind": "0 in",      "Cloud Cover": "12 mph NNW",      "Pressure": "24%"    },    {…

 

This will respond with a 200 OK and the data is visible when you explore it in the Power BI UI.

 

 

This has been a brief introduction to the Power BI APIs. In the comments below, please let us know what ideas you have for Power BI applications and which APIs you would like to see next.