Power BI APIs for Non-Programmers

Level: Intermediate

There was an announcement made a few weeks ago by Microsoft that nearly slipped under my radar.  I don’t always read the more technical announcements because I don’t have a highly technical background.  Luckily I read this particular announcement as it contained a nugget of gold that is very useful for anyone with a business background that wants to learn more about the Power BI APIs.

What is a Power BI API

API stands for Application Programming Interface.  In simplistic terms, it is a way that programmers can interact with an application – in this case the application is PowerBI.com.  Normally you need to use one of a number of different tools to be able to “call” an API.  Some of the easier to use tools include PostMan, Power Shell, and also Microsoft Flow.  I blogged about how to use Microsoft Flow to call the Power BI APIs in a series of blogs starting here.

Despite the above tools being “relatively” easy to use, there is still a lot to learn, and the learning is not for the faint hearted business user.  But all that changes with the new “Try It” tools.

Power BI APIs Try It Tools

What Microsoft announced in this article was the ability for anyone (with access approval) to run any of the APIs by simply clicking “Try It” directly from within the online documentation.  This is amazing, because now anyone can use any of the APIs without having to know anything about programming.

Useful Power BI APIs

There are a lot of Power BI APIs available and they all have their purpose.  I can’t go into them all but you can read about them here https://docs.microsoft.com/en-us/rest/api/power-bi/  For this blog I am going to show you how I extracted a list of all Workspaces from my tenant and produced a list in Excel.

When using the API, a “Group” refers to an app workspace. You can use the “get groups” API to get a list of all the workspaces in your tenant.

Here is the documentation

https://docs.microsoft.com/en-us/rest/api/power-bi/groups/getgroups

I navigated to the API documentation link above and clicked “Try It”.

I was asked to sign into my account.  I have a few, so I was presented with a list.

After signing in, I was presented with some optional parameters (1 below).  I left these blank and clicked “Run” (2 below).

The API ran, and the results were returned to me in the browser as shown below.

This is a JSON document. This file format is pretty common these days, and you can look at it and work out what is going on.  But I decided to copy the JSON code and use Power Query to make it easier for humans to read.

Turning JSON into a Table

I copied the JSON document by clicking “copy” in the top right hand corner (as can be seen in the image above).  I then pasted the JSON code into a text file called groups.txt using Notepad.exe.

Using Power Query for Excel, I connected to the text file and went into edit mode using the defaults suggested by Power Query.  By default, Power Query treats the file as text. It is easy to change this file type to JSON.  Just click on the cog next to Source (shown below).

Then you can  set “Open file as” to JSON.

After clicking OK, I was presented with the following.

I clicked on the “List” above, to drill into the list of results from the JSON file.

I then converted the list to a table (click “Convert -> Into Table”), and then expanded the table by clicking the icon shown below.

And that’s it.  Power Query converted the JSON document into the table below.

Now I have this Power Query conversion file, I can update the groups.txt file at any time by re-running the API, and then see the list of workspaces in my tenant.  There are many other uses for the APIs and now it is easy to use them, why not spend a few moments exploring what is possible.

Get Datasets in Group

I was exploring the options above (taking my own advice) and I found this API. https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getdatasetsingroup  It allows me to get a list of datasets from a specified group.  I already have a list of my groups from the exercise above, so I ran this API.

This time I was asked for the GroupID as one of the mandatory parameters.

I copied one of the Group IDs from my Excel spreadsheet from above. I repeated the Power Query process and then I had a list of all my datasets in one of my workspaces

When I extracted the list above, I realised that the second item “Business Driver Chart V3.0” had been loaded by mistake.  I then used this API https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/deletedatasetingroup to delete the dataset from the workgroup – it worked a charm.  Pretty easy to do, when you know how.

How Will You Use the APIs?

I would love to hear from people how they are using the APIs to solve problems.  Please share in the comments below.