How get data using API with username and password.

[Anonymous]

Hi there,

Am new to Power BI and little knowledge of how APIs work, so I stand to be guided if am so ambiguous.

Now, we have a web application in which data is entered for the different data elements we work with. Every end of week, i have to pull data from the system in form of a csv file and analyze it in excel. All works well but now I have started working with Power BI and instead of downloading a file every week and then copy it to the right directory, I would like to be able to connect and get the csv/data directly into Power BI.

How am doing it currently:

The app admin provided me with a URL (API) where I specify the data set UID, start date, end date and organizational unit for the data I want to extract.

When I paste the link in a browser, am prompted to provide my username and password. Once validated, the file downloads.

Below is URL format:

https://mydomain.com/ehmis/api/dataValueSets.csv?dataSet=hmLx1G0pryP&startDate=2020-09-28&endDate=20...

The URL specifies:

DataSet UID =hmLx1G0pryP

StartDate =2020-09-28

endDate 2021-02-28

orgUnit  =LQ1q6iX8dzR

And whether I want to download data for child orgUnits (lower organizational units) = true

What I would like to Achieve:

From power BI, I provide the URL above, include the username and password; and get the data/csv file directly into Power BI without downloading it elsewhere.

Is this achievable with the information I have provided?

Thanks for your help..

[PhilipTreacy]

Hi @Anonymous 

The problem is the bit in bold

When I paste the link in a browser, am prompted to provide my username and password. Once validated, the file downloads.

Power BI has no way to interact with web pages and enter usernames and passwords into forms.

A proper API should provide a means by which you can authenticate that doesn't require you to manually (or otherwise) enter usernames/passwords into forms.  This could be as simple as providing the username and password as query parameters in the URL.

If your API cannot do this then I'm afraid you are a bit stuck.  You could use VBA to drive a web brower session through which it is possible to interact with forms andenter usernames/passwords.  VBA could then download the data and you could then get it into PBI but I realise this isn't the ideal situation that you are aiming for.

Here's an article that describes how to use VBA to interact with login forms

https://www.myonlinetraininghub.com/web-scraping-filling-forms 

Regards

Phil