How to GET data from an API using POWER QUERY in Power BI | Food Bank & Job Search

[@OrchestraDataPlatform]

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!

[@usersg544]

Very Helpful !

[@gloeni]

This was useful. Thanks alot

[@NaviHaviWorld]

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???

[@DeanChereden]

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

[@NS-tp3vr]

How we can access api data into an excel having bearer token?

[@DeanChereden]

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

[@dipereira0123]

its not that easy anymore, if you use powerBI dataflows, it will require a Gateway =(

[@DeanChereden]

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.

[@NguyenPhuc-ot2ch]

How about the price when I use this solution in production?

[@DeanChereden]

Hey, what do you mean by price? Refreshing in the service?

[@x17aerialconsultancy51]

SUBSCRIBER 767 😀

[@rezianaduzha3574]

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?

[@DeanChereden]

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

[@whyt1474]

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?

[@DeanChereden]

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 🙂

[@pratikfutane8131]

Great video!!