Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

REST API Data Pull: Possible when API requires call for token first (user input)?

I would like to directly connect Power BI to data that is accessible by a REST API Endpoint. However, to access this data-containing endpoint, I first have to use my Web SSO username and password to get an authentication token from a separate API endpoint. This token will be valid for 12 hours for the same user. Using this token, I can go to the original endpoint, authenticate, and pull the data that I want. So far, I've just been doing all of that in Python. After I get the JSON data in Python, I convert to an Excel, and then use that Excel as a source for Power BI. It's not very efficient.

 

My question: Can I do all of this in Power BI Desktop?

 

The API Endpoints (example links - not allowing unlink for some reason):

  1. Log-in Endpoint: www.business.com/API/login
    1. This is where I need to give an input of my username and password. In return, I will receve an authentication token that is valid for 12 hours.
    2. I will need some way to hide my password if this API call is being done in Power BI - I don't want other report developers or viewers to see my password.
  2. Actual Data Endpoint: www.business.com/API/DataEndpoint
    1. This is where I will get my actual data from. This is time series data that is updated frequently.
    2. I will also need some way to control what data I am pulling. For example, I will need to specify whether I want data from 2005-2015 or from 2020-2022. 
    3. I will recieve data in a format that will require a good amount of unpacking and transforming. Currently, this is all done in Python. I have a Python script that handles the API output, and transforms it into an easy-to-understand format. I just feed that to Power BI for now.

 

Is is possible to do this in Power BI Desktop? Or maybe, is there some way to integrate my Python script? As in, there's a Power BI mechanism that calls my Python script, asks the user for log-in input(username, password), gets the Authentication Token, then asks the user for what data they want from the actual endpoint, uses their inputs from that and the previously recevied Token to get all the data? This would make the data-gathering process a lot cleaner, as I don't have to use a bunch of Excel files. Plus a report viewer could bring in new data from the API Endpoint, if they have the access rights to log-in to it.

1 REPLY 1

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.