Ever wonder how live Power BI dashboards display real-time data like weather, stock prices, or social media trends?
The solution is not complex: REST APIs - blankets that aren't seen but provide a way to establish a connection between Power BI and an infinite realm of data that can be found online.
This guide is intended for new APIs and Power BI users. You will know what are the REST APIs, how Power BI is connected to them, how to authenticate, what problems may occur in general, and how to build a real-time weather dashboard showing the current temperature in Nairobi, Mombasa, Kampala, and Kigali.
At the conclusion, you will have developed the API novice into a data explorer.
What Is a REST API?
Imagine a restaurant. You are the customer, the kitchen is the data server and the waiter is the API. One can make a request to the waiter to select something particular, say, what is the current temperature in Nairobi. The waiter is introduced to the kitchen and requests what you have ordered, goes and gets the information and comes back with it on a plate all prepared to eat.
That is precisely what an API does. It enables you to query a source of data and get them in a more structured format, typically in the form of a JavaScript Object notation (JSON) that can be easily read by Power BI and converted into visuals.
Methods of integrating Power BI with a REST API.
Power BI provides numerous alternatives to access APIs, which will depend on the complexity of the API and the type of information required.
Using "Get Data - Web"
This is the simplest and least complicated alternative.
Steps:
Home - Get Data - Web (in Power BI Desktop).
Paste your API link. For example:
https://api.openweathermap.org/data/2.5/weather?q=Nairobi&appid=YOURAPIKEY
The data will be fetched and will be displayed in the form of JSON by Power BI. Then clean, transform, and visualize it.
The technique is ideal in straightforward or open APIs that do not require intricate authentication.
Using Power Query M Code
Want to have more control, such as adding parameters, adding headers, or making a chain of API calls? The Power Query language (M) of Power BI allows creating your own script.
Example:
let
// === Settings ===
ApiKey = "YOUR API KEY",
Cities = {"Nairobi,KE", "Mombasa,KE", "Kampala,UG", "Kigali,RW"},
// === Function to call OpenWeatherMap for a single city ===
GetWeather = (city as text) =>
let
url = "https://api.openweathermap.org/data/2.5/weather?q=" & city & "&appid=" & ApiKey & "&units=metric",
Source = Json.Document(Web.Contents(url)),
// convert record to a table so it matches your original expansion logic
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded coord" = Table.ExpandRecordColumn(#"Converted to Table", "coord", {"lon", "lat"}, {"coord.lon", "coord.lat"}),
#"Expanded weather" = Table.ExpandListColumn(#"Expanded coord", "weather"),
#"Expanded weather1" = Table.ExpandRecordColumn(#"Expanded weather", "weather", {"id", "main", "description", "icon"}, {"weather.id", "weather.main", "weather.description", "weather.icon"}),
#"Expanded main" = Table.ExpandRecordColumn(#"Expanded weather1", "main", {"temp", "feels_like", "temp_min", "temp_max", "pressure", "humidity", "sea_level", "grnd_level"}, {"main.temp", "main.feels_like", "main.temp_min", "main.temp_max", "main.pressure", "main.humidity", "main.sea_level", "main.grnd_level"}),
#"Expanded wind" = Table.ExpandRecordColumn(#"Expanded main", "wind", {"speed", "deg", "gust"}, {"wind.speed", "wind.deg", "wind.gust"}),
#"Expanded clouds" = Table.ExpandRecordColumn(#"Expanded wind", "clouds", {"all"}, {"clouds.all"}),
#"Expanded sys" = Table.ExpandRecordColumn(#"Expanded clouds", "sys", {"country", "sunrise", "sunset"}, {"sys.country", "sys.sunrise", "sys.sunset"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded sys",{
{"coord.lon", type number}, {"coord.lat", type number}, {"weather.id", Int64.Type},
{"weather.main", type text}, {"weather.description", type text}, {"weather.icon", type text},
{"main.temp", type number}, {"main.feels_like", type number}, {"main.temp_min", type number},
{"main.temp_max", type number}, {"main.pressure", Int64.Type}, {"main.humidity", Int64.Type},
{"wind.speed", type number}, {"wind.deg", Int64.Type}, {"name", type text}
})
in
#"Changed Type",
// === Call function for each city and combine ===
WeatherTables = List.Transform(Cities, each GetWeather(_)),
Combined = Table.Combine(WeatherTables),
#"Reordered Columns" = Table.ReorderColumns(Combined,{"sys.country", "name", "coord.lon", "coord.lat", "weather.id", "weather.main", "weather.description", "weather.icon", "base", "main.temp", "main.feels_like", "main.temp_min", "main.temp_max", "main.pressure", "main.humidity", "main.sea_level", "main.grnd_level", "visibility", "wind.speed", "wind.deg", "wind.gust", "clouds.all", "dt", "sys.sunrise", "sys.sunset", "timezone", "id", "cod"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"sys.country", "country"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"country", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"id", "cod", "timezone", "weather.icon"})
in
#"Removed Columns"
This will provide you with freedom to call automation, deal with many cities, and control more sophisticated processes.
With Custom Connector or Azure Function.
Beyond advanced configurations, businesses often use custom Power BI connectors or Azure Functions to handle authentication and data processing before sending clean data to Power BI.
This is ideal for sensitive, high-volume data requiring frequent updates.
Power BI Authentication.
To connect to APIs, Power BI must be granted data access permission via authentication, which Power BI supports in common forms.
1) Anonymous
Applied to public APIs that do not need any form of logins. You are able to stay in touch with each other without unnecessary procedures.
2) API Key
This is the most widespread technique. You append your key to an API URL or a header. It is saved safely in the Data Source Settings of Power BI.
Example:
https://api.openweathermap.org/data/2.5/weather?q=Nairobi&appid=YOURAPIKEY
3) Basic Authentication
Unlike other APIs that use a username and password for authentication, Power BI securely stores your credentials after you enter them.
4) OAuth 2.0
APIs requiring user authorization, such as Google or Microsoft Graph, utilize a secure login window for authentication. Upon signing in, Power BI receives a temporary token granting access. Always consult the API documentation to confirm the authentication method. Importing API data into Power BI can present challenges.
Integrating Power BI with an API can be exciting, but it's important to be aware of potential pitfalls.
1) Pagination
Most APIs do not provide a large number of records on a request. Power Query loops or functions will be required in order to access all the data pages.
2) Rate Limits
Other APIs limit the frequency of requests made e.g. 60 calls per minute. Going beyond this will have your key blocked temporarily.
3) Authentication Expiration
The use of tokens in APIs (such as OAuth) may have expiry times and should be automatically renewed otherwise there may be refresh errors.
4) Nested JSON
The API data is usually embedded in layers, such as boxes within boxes. The Power Query editor on Power BI allows one to expand and flatten these fields into a clean table.
5) Cloud Refresh
When publishing to Power BI Service, ensure your credentials and gateways are properly configured for scheduled refresh.
Integrating OpenWeatherMap API data with Power BI.
To be more practical, we will consider linking Power BI to the OpenWeatherMap API to get the current temperature of Nairobi, Mombasa, Kampala, and Kigali.
Step 1: Get Your API Key
Obtain your API key from the open data weather site by creating a free account.
Copy your special API key (long sequence of characters and numbers).
Step 2: Develop a Power Query Function.
In Power BI:
Home -Get Data - Blank Query - Advanced Editor.
This provides an interactive feature that recurs and gets weather information for any passing city.
Step 3: write the below code in the advanced editor
et
// === Settings ===
ApiKey = "Api Key",
Cities = {"Nairobi,KE", "Mombasa,KE", "Kampala,UG", "Kigali,RW"},
// === Function to call OpenWeatherMap for a single city ===
GetWeather = (city as text) =>
let
url = "https://api.openweathermap.org/data/2.5/weather?q=" & city & "&appid=" & ApiKey & "&units=metric",
Source = Json.Document(Web.Contents(url)),
// convert record to a table so it matches your original expansion logic
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded coord" = Table.ExpandRecordColumn(#"Converted to Table", "coord", {"lon", "lat"}, {"coord.lon", "coord.lat"}),
#"Expanded weather" = Table.ExpandListColumn(#"Expanded coord", "weather"),
#"Expanded weather1" = Table.ExpandRecordColumn(#"Expanded weather", "weather", {"id", "main", "description", "icon"}, {"weather.id", "weather.main", "weather.description", "weather.icon"}),
#"Expanded main" = Table.ExpandRecordColumn(#"Expanded weather1", "main", {"temp", "feels_like", "temp_min", "temp_max", "pressure", "humidity", "sea_level", "grnd_level"}, {"main.temp", "main.feels_like", "main.temp_min", "main.temp_max", "main.pressure", "main.humidity", "main.sea_level", "main.grnd_level"}),
#"Expanded wind" = Table.ExpandRecordColumn(#"Expanded main", "wind", {"speed", "deg", "gust"}, {"wind.speed", "wind.deg", "wind.gust"}),
#"Expanded clouds" = Table.ExpandRecordColumn(#"Expanded wind", "clouds", {"all"}, {"clouds.all"}),
#"Expanded sys" = Table.ExpandRecordColumn(#"Expanded clouds", "sys", {"country", "sunrise", "sunset"}, {"sys.country", "sys.sunrise", "sys.sunset"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded sys",{
{"coord.lon", type number}, {"coord.lat", type number}, {"weather.id", Int64.Type},
{"weather.main", type text}, {"weather.description", type text}, {"weather.icon", type text},
{"main.temp", type number}, {"main.feels_like", type number}, {"main.temp_min", type number},
{"main.temp_max", type number}, {"main.pressure", Int64.Type}, {"main.humidity", Int64.Type},
{"wind.speed", type number}, {"wind.deg", Int64.Type}, {"name", type text}
})
in
#"Changed Type",
// === Call function for each city and combine ===
WeatherTables = List.Transform(Cities, each GetWeather(_)),
Combined = Table.Combine(WeatherTables),
#"Reordered Columns" = Table.ReorderColumns(Combined,{"sys.country", "name", "coord.lon", "coord.lat", "weather.id", "weather.main", "weather.description", "weather.icon", "base", "main.temp", "main.feels_like", "main.temp_min", "main.temp_max", "main.pressure", "main.humidity", "main.sea_level", "main.grnd_level", "visibility", "wind.speed", "wind.deg", "wind.gust", "clouds.all", "dt", "sys.sunrise", "sys.sunset", "timezone", "id", "cod"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"sys.country", "country"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"country", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"id", "cod", "timezone", "weather.icon"})
in
#"Removed Columns"
Step 4: Build the Dashboard
Import the data into Power BI and draw a bar chart, table or map of the temperatures of the four cities. Even refreshing it automatically can be scheduled.
Final Thoughts
REST APIs open up a world of live data for Power BI users. Whether you want to track weather, financial markets, or social media activity, APIs can feed real-time information directly into your dashboards.
You’ve learned how Power BI connects to APIs, how it handles authentication, what challenges to watch out for, and how to build a real example using OpenWeatherMap.
Once you get comfortable with APIs, you’ll realize that Power BI isn’t just about charts and tables; it’s your window into the living, breathing data of the internet.
Top comments (0)