Connecting Power BI to REST APIs Endpoints.

#api #restapi #datascience #data

APIs power almost everything today — from live weather dashboards to business systems.
And when you combine them with Power BI, you unlock real-time, automated reporting from virtually any web data source.

In this article we'll explore;

Different ways to connect Power BI to a REST API endpoint?
How does Power BI handle authentication when connecting to a web API?
What are some challenges you might face when importing data from an API into Power BI?

## Different ways to connect Power BI to a REST API endpoint.
There are several approach when one wants to connect to APIs from simple to fully dynamic connections.

_ 1. By simple GET requests or simply using a web connector._
This method is one of the easiest ways to fetch data from endpoints especially those that use public APIs.
In Power BI;
- Go to Get Data → Web
- Paste your API endpoint (e.g. https://api.openweathermap.org/data/2.5/weather?q=Nairobi&appid=YOUR_API_KEY&units=metric)
- Click OK
Power BI should automatically detect and parse the JSON format.

2. Power Query(M Language)
Here one fetches data in json format and parse the data itself dynamically.
This function better when one wants to loop through endpoints or build custom logics.
let Source = Json.Document( Web.Contents("https://api.openweathermap.org/data/2.5/weather", [Query = [ q = "Nairobi", appid = "YOUR_API_KEY", units = "metric" ]] ) ), main = Source[main], temperature = main[temp] in temperature

3. Using custom connectors.
Writing you own connector in power query SDK or M code and packaging it when connecting to complex APIs or reusing across reports.

4. Using python/R scripts.
Power BI also supports Python and R, so you can use them to make API calls.
Example in a code;
`import requests, pandas as pd

url = "https://api.openweathermap.org/data/2.5/weather"
params = {"q": "Nairobi", "appid": "YOUR_API_KEY", "units": "metric"}

response = requests.get(url, params=params)
df = pd.json_normalize(response.json())
`
You should then import this script into Power BI as a data source.

How Power BI Handles Authentication When Connecting to APIs.

When you connect Power BI to a web API, it prompts you to choose an authentication method. Supported types include:,

Anonymous: No login credentials required mostly for public APIs.
Basic: Username and password are essential for requests.
Web API: Api keys/tokens are required and are passed via query.
Windows.
Organizational accounts.

Challenges faced importing data from Power BI.

Working with APIs inside Power BI comes with a few hurdles.
And they are;

Authentication - some API keys may expire while refreshing.
Pagination - large datasets may split across pages.
Rate limits - APIs may restrict number of requests made maybe per day or hourly.
API key exposure - storing API keys might be risky since they can be visible especially when using queries.
Complex JSON structure - nested objects make it hard to expand data.
Dynamic endpoints- some URLs might change with each requests.

Integrating Openweather API in the easiest way.

Step 1: Create a blank query.
In Power BI:

Home -> Get Data -> Web -> Basic

In the URL section under basic paste a similar code as this: https://api.openweathermap.org/data/2.5/weather?q=Nairobi&appid={API KEY}&units=metric

This will in turn give you data of the climate in Nairobi in form of a table.
Repeat the same process to get data of the other cities then append the tables to form one table.
After fetching all data, clean and remove unnecessary columns or rename some columns if necessary.