Many times a client wants some sort of customized view of the data they have in PowerBI. The truth is, as powerful as it is PowerBI, like all BI tools, offer limited customizability. However, PowerBI comes with a powerful RESTful interface that we can use to show the underlying data however we wish.
This has the benefit of using PowerBI’s caching, aggregation engine, and many other unique enhancements and insights to the data in our own custom interface. This can be anything from a webapp to a mobile app to specific hardware.
In this quick post I’ll show you the basics of using a PowerBI dataset in a custom webapp via REST.
Requirements
Access to an Azure subscription (you can get one for free)
Access to PowerBI
A Github account (it’s free)
Visual Studio Code
NodeJS
Setup
React Application
We will be cloning a repository, you do not have to manually create the react app at this point.
VS code Extensions
Within VS code install both the Github extension and the Azure Static Web Apps extension.
Once both are installed, we will link github to vs code. This guide shows how to link the account, however you don’t need to create a repository just yet as that will be done in a later stage.
Creating the webapp
Now, from the Azure extension, we will select the “create new webapp” button. This should walk you through the motions of creating the application in azure as well as creating a new repository in Github.
Once this is done, you can go to source control and create an empty commit. This will publish your webapp to Azure.
If you go to your Azure portal under static webapps, you should see the url for your webapp.
Getting PowerBI ready
Creating a dataset
For this purpose, we’re going to create a small dataset in PowerBI with some data that we will later aggregate in order to show some KPIs.
The Dataset
I created an excel sheet with some sample data.
Year
Sales
Customers
2022
20000
120
2021
25000
130
2020
21000
150
I imported the dataset into PowerBI and grabbed the ID from the URL as we’re going to need it later.
Enabling REST
In order to use the PowerBI REST apis, we will need to enable it first. We do this by registering the app with Azure. Here is a guide on how to do this. We will use the embed for my organization option and once you’re done, you should have an app id and secret. When selecting the APIs that will be available, you can select Read all reports and Read all datasets.
Adding Endpoints
This would have created an app registration for you in Azure.
Go into your apps (under Home -> App Registrations) and you should see it there.
Add the endpoints for testing your app. Ultimately, you will be adding your webapp’s final domain to it.
The above application has a tab named Tenant. It makes use of the REST API through fetch. We will duplicate this and modify the fetch.js file a bit to handle the POST requests that PowerBI needs to make.
Modifying fetch.js
Go to src/fetch.js of your sample application and modify the file as such:
By doing this, we’re adding the capability to modify the method, the headers, and submit a body.
Creating a KPI tab
Go to the Tenant.jsx file, duplicate it, and rename it to KPIs.jsx. We will be modifying it as below.
The modifications include calls to the newly configured PowerBI endpoints, creating KPI boxes from the PowerBI data, and coloring the boxes.
* The `MsalAuthenticationTemplate` component will render its children if a user is authenticated
97
* or attempt to sign a user in. Just provide it with the interaction type you would like to use
98
* (redirect or popup) and optionally a [request object](https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-browser/docs/request-response-object.md)
99
* to be passed to the login API, a component to display while authentication is in progress or a component to display if an error occurs. For more, visit:
Type in npm run build in your VS terminal and a browser should popup with your application.
Navigate to the “KPIs” tab that you created and click on “Request Dataset Data”.
It should go through the process of authenticating you and you should be able to see your KPIs live fed from PowerBI!
Conclusion
There you have it! Although this was a simple example, you can easily see how we’re able to fully manipulate the way we want to show PowerBI’s data. This can be used in a number of applications, not just web. Can you think of any other applications? Do you find this useful? Reach out to me on LinkedIn!
Roberto is an engineer (M.S.) with a passion for automation, data-driven solutions, and technology in general. He is almost always talking about something new in the technology world and enjoys learning about it. He's also done some pretty interesting stuff both personal and professional.
Currently, he develops solutions for all sorts of clients at Perficient, Inc. His strongest competency is Analytics and Business Intelligence (MicroStrategy, Tableau, PowerBI), but has experience designing a plenitude of solutions and integrations ranging from front-end and client-facing to back-end, transactional and server-side.
Prior to Perficient, He worked at MicroStrategy, Inc. where he helped make the product better by guiding his team through performance and reliability testing as well as dealing with escalated customer issues. Before that he worked as a project engineer and web developer.