How to GET data from an API using POWER QUERY in Power BI | Food Bank & Job Search
Вставка
- Опубліковано 1 сер 2024
- In this video, I show how easy it is to get data from an API using power query in power BI, although this will work with power query in excel too, for an API that does not require a key, and an API that does require a key.
📁 Give Food API documentation -
www.givefood.org.uk/api/2/docs/
📁 Reed.co.uk API documentation -
www.reed.co.uk/developers/job...
🔖c h a p t e r s 🔖
00:00 - Intro
00:38 - API - No Key - Give Food
02:17 - Load API (no key) into PBI
04:20 - API - with Key - Reed.co.uk
06:41 - Load API (with key) into PBI
💌 My email - dean@deanchereden.com
🌍 My website - www.deanchereden.com
🐦 Twitter - / deanchereden
🎵 Music - Misha, Jussi Halme - Higher chll.to/4ec991f9
🙌 Follow Misha
・ SoundCloud: chll.to/386e721b
・ Spotify: chll.to/5f0bc9ba
・ Apple Music: chll.to/5a239af3
・ Chillhop: chll.to/2b2bc310
🙌 Follow Jussi Halme
・ Spotify: chll.to/dd4a82d2
・ Apple Music: chll.to/4961c878
・ Chillhop: chll.to/eb17b45e
🎶 Listen to Chillhop Music - • Misha & Jussi Halme - ...
Check out Orchestra - this sounds like you'd want to build an E2E pipeline to feed Power BI rather than just query from an API off the cuff!
Very Helpful !
This was useful. Thanks alot
Great video!!
Hi thanks for the video but if i have pagination for my api i need to hit api in loop for 10 pages data in power bi???
Hi, you'll need to add the loop to the url of the API that picks up the page number. It should be covered in the API documentation. An example of what this would look like in Power Query is:
let
// Base URL and other parameters
BaseUrl = "api.example.com/data?page=",
// Function to fetch data for a specific page
GetPage = (Page as number) as table =>
let
Source = Json.Document(Web.Contents(BaseUrl & Number.ToText(Page))),
Data = Source[Data]
in
Data,
// Number of pages to fetch
TotalPages = 10,
// Generate list of pages and get data
Pages = {1..TotalPages},
Data = List.Combine(List.Transform(Pages, each GetPage(_)))
in
Data
Hi what if my API is RPC and i should send a json to request data, not only the link . How can i do this?
Hi, If you're dealing with an API that uses Remote Procedure Call (RPC) and requires a JSON body to be sent with the request, you can handle this in Power Query (within Excel or Power BI) by specifying the JSON body in the Web.Contents function.
You'll need to create the M Code in the advanced editor and include your bearer token too. Example of how this would look is:
let
// Base URL for the API
BaseUrl = "api.example.com/rpc",
// Bearer token for authorization
Token = "YOUR_BEARER_TOKEN",
// Define the JSON body as a text string
JsonBody = Json.FromValue([
jsonrpc = "2.0",
method = "getData",
params = [param1 = "value1", param2 = "value2"],
id = 1
]),
// Make the API request with the JSON body
Source = Json.Document(Web.Contents(BaseUrl, [
Headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & Token
],
Content = Text.ToBinary(JsonBody)
]))
in
Source
How we can access api data into an excel having bearer token?
Hi, for bearer tokens you can build them into the api call url to make them active, than using them as your log in.
If you use Power Query in Excel, it's the same as Power BI, you just need to go to Get Data and select 'From Web' and add in the api url.
Once the transformation page loads up, go to advanced editor to edit the M Code and use the below example as you base to update with your API url and bear token:
let
// API URL - Change the url to your API url
BaseUrl = "api.example.com/data",
// Bearer token - change YOUR_BEARER_TOKEN to the bearer token you want to use
Token = "YOUR_BEARER_TOKEN",
// API Request with headers
Source = Json.Document(Web.Contents(BaseUrl, [
Headers = [Authorization="Bearer " & Token]
]))
in
Source
How about the price when I use this solution in production?
Hey, what do you mean by price? Refreshing in the service?
This is great! I am trying to get the data with an API key. My API link opens in the browser, yet when I try to open the API link as you showed in Power BI it states "Details: "Access to the resource is forbidden." Is there a solution you can suggest?
This might be something to do with global permissions.
If you go to options and settings and select the data source settings option.
Click on the button for 'Global Permissions', find the API link, select it and click on Clear Permissions.
This should hopefully reset the link so you can try and connect again.
Hope it works 🙂
SUBSCRIBER 767 😀
its not that easy anymore, if you use powerBI dataflows, it will require a Gateway =(
Dataflows are a different beast compared to desktop. No idea why MS decided to make them different. You can't even use all connectors in dataflows but can in desktop.