Power BI REST API

Like most modern servers nowadays, Power BI has a fairly good set of web APIs that developers can leverage to extend functionalities of the product and further automate many processes. The developer’s section of the documentation provides an easy to follow set of instructions on how to get started and showcases some very useful feature I really like (that’s also not in the Tableau equivalent) including dataset refresh, data inserts and dataset creation. While easy to follow, the part on authentication is not exactly clear and documentation is anything but complete. This, and some other  potholes if you’re not careful is evident in the StackOverflow statistics on the subject (on StackOverflow alone, it’s been viewed over 14000 times).

What follows is hopefully some clarity and alternatives to help other developers who may get stuck on this. I’ll just focus on the code as the other perquisites like registering your app with Azure AD is clear and straight forward.

Below is the code for authentication straight from Microsoft. First thing to be aware, the wrapper version here is critical, so use it unless if you want to experiment or search the net all night to find something that works for the latest version.

Power BI REST API-Sam Tran

In it’s current form, the code above will launch the form in the screenshot below for you to sign in to get the token for your subsequent requests. A good question many have rightly asked is, how do I perform a ‘silent’ automated sign in. Unfortunately, the answer is not on the demo, Nuget or the Power BI REST API pages.

Power BI REST API-Sam Tran

There’s several alternative but if you want to continue using the wrapper from Microsoft, you can update your code to this below. Note the async method and that you now need to pass in your credentials (login/password). Note also redirect URL is not longer needed.

Power BI REST API-Sam Tran

My preferred approach is to forgo the wrapper altogether and just use bare codes like this below. Fewer dependencies mean easier implementation/transportation to SSIS, CLR Procs or Azure functions and apps as well as easier translation to other languages like Java or Python.

Power BI REST API-Sam Tran

For those that don’t code but would like to leverage the power of the APIs, its definitely worth looking at Microsoft Flow.  This blog provides a useful step by step for those new to Flow.

And finally, some examples of what can be done and how easy it is to do it, once you’ve been authenticated and have those damned token…

This creates a empty dataset on Power BI

Power BI REST API-Sam Tran

To refresh a dataset

Power BI REST API-Sam Tran

And one of my favourites, adding data to a dataset

Power BI REST API-Sam Tran

Happy coding!

 


Leave a Reply