- What we do
- Who we work with
- Who we are
Power BI can connect to a wide variety of systems to collect data to drive reports and dashboards. But what if we want to consolidate data from multiple locations, such as a large number of dynamically created SharePoint lists, into a single dataset?
You might be tempted to first consolidate this data into a location such as Azure SQL, before connecting to that SQL database from Power BI. However, this isn’t actually necessary, as we can use Power BI’s own API to create a dataset into which we can push data directly, removing the need for a separate database.
This blog post outlines the steps involved in using the Power BI API to manage data. These are:
This first blog post covers app registration, user consent and authentication, while the second part will cover using the Power BI API.
To get started with the Power BI API, you’ll first need to configure a new App Registration in the relevant Azure Active Directory (in this case,
blogdemo.onmicrosoft.com). In the ‘required permissions’ section, we have to specify what permissions our application needs to access online services managed through Azure AD. In this case, we want to give full access to Power BI:
In practice you should give an application only the minimum permissions it requires to function, but we’ll ask for everything to keep this demo straightforward. Adding these permissions doesn’t actually give the application access to Power BI – it only indicates that the application wants these permissions. Users must consent to this application having this access – we’ll see how this happens shortly.
From the app registration we need to take a note of the application ID (
0ee9fc79-861f-4d34-a439-2c3a4f88c171), and generate and take note of a key. The application ID is like a username for the application, while the key is like a password. We also need a reply URL (which is set during the initial creation of the app registration) – in this case we are using
As I mentioned earlier, an app registration only lists the permissions requested by an application, rather than granting them. Before an application can access Power BI as a user, the user has to consent to this happening. This is done by the user following a special link to Azure AD that will look something like this:
This URL contains the reply URL and application ID (passed in as the
client_id) from our app registration. Once a user follows this link and authenticates by entering their username and password, they are asked to consent to the list of permissions we asked for when creating the app registration:
By clicking on ‘Accept’, the user (in this case Alice) consents to our application having those permissions on her content in Power BI. Now that we have our App registration and a user has granted consent, it’s time to actually create our application!
Before we can do anything with the Power BI API, we need to authenticate and acquire an access token that we can then pass into all our calls to the API. To authenticate we perform an HTTP POST to
https://login.microsoftonline.com/blogdemo.onmicrosoft.com/oauth2/token, passing in the following values:
0ee9fc79-861f-4d34-a439-2c3a4f88c171(our Application Id)
If this POST succeeds, we get back a chunk of JSON that contains a large, impenetrable-looking
access_token value. That value is actually a JSON web token (JWT) that we can decode, which can be invaluable in trying to work out why things haven’t worked:
Now that we have this token, we can actually start to use the Power BI API – which I’ll look at in the next blog post in this series.
Microsoft Office 365 Future thinking Cloud Collective intelligence Intranets Business intelligence Digital Transformation Change Management Office Technical Business Transformation 20th Anniversary Gold Partner Privacy Microsoft Inspire CRM Partners Director's Briefing Public Sector Training Websites