Use Power BI to loop through multiple pages of an API

[@workstuff5253]

You sir are a god send!! Just when I was about to through in the towel with the refresh problems.

[@PowerBIFerryTales]

Thank you. It is a frustrating issue to have to deal with, so I'm really happy to see it helped you out. 🙂

[@MrZdzisz]

Whoa, what a lesson. Concise and effective. Thank you!

[@twatkins633]

Exactly what I needed. Thanks for taking the time to make this video!

[@PowerBIFerryTales]

Thank you! I'm really pleased it came in handy.

[@johndonnelly3934]

I'm close on this, but encountering errors when I invoke the function. I'm thinking it is likely due to a Headers clause with 3 other parameters that need to get passed to the API as part of the JSON call. A Pubic Key, ClientID, and Authorization code after the url. Error is - Expression Error: 3 arguments were passed to a function which expects between 1 and 2. Details: Pattern= Arguments=[List] Thoughts/feedback? I'm assuming that the RelativePath comes before Headers, but uncertain of punctuation.

[@seamushand8439]

You removed a lot of the black magic from API for me. Thank you

[@darkmatter5310]

Very helpful and elaborate information.

[@ahmedmegahedcyc]

Thanks for your efforts. Really appreciate it. What I search for

[@mathieudager4234]

Neat. Really just neat. Big Up. Thanks for sharing

[@ОльгаГонца-у2о]

Very helpful video. Thanks! I have a question. If I invoke the function to all list values (players in your case) - is these data keeps in data flow? or every time the data is getting from API?

[@MrAdnanfayyaz]

How to add two different parameters from two different tables?

[@jaysonbarrientos2298]

Thanks for this! Helped a lot!

[@PowerBIFerryTales]

Thanks, I'm happy it helped you!

[@אלכסנחשונוב-פ5ע]

Wow, Fantastic!!! Thank you so much

[@arturotorres4903]

Is this possible if we have 2 or more parameters that we wanted to change?

[@Jpalfarog]

Excelent!!! you really help me thanks!

[@SamVeneman]

If your API call has authorization where do you put the headers in the code?

[@PowerBIFerryTales]

Precisely what to write depends on the API, so best check the documentation, but this usually works: Source = Json.Document(Web.Contents("URL FIRST PART", /* URL VARIABLE */ [RelativePath =URL VARIABLE AGAIN] & [Headers= [Authorization= "Basic XXXXXXXXX"] & [Accept = "application/json"]])),

[@nilangikapade1657]

That was really helpful. Thanks a ton!!!

[@DanielVoyles]

If an API is passing the variable in the body of the JSON package with double-quotes required, how do you enter the variable? I have tried ""&(id)&"" and several other iterations, but nothing seems to work.

[@JacobClements-f3f]

For some reason this works for me, but the data stops at page 50. Is there a way to get around this?

[@lehlohonolomakoti7828]

Amazing video, right to the rescue 👏👏

[@saadnageeb4633]

Thanks so much ,it worked very fine with me.

[@imranalishah2922]

Thanks for the video, i have question, what if I have to define relative from SOAP API Body, the parameter for my API exists in body of SOAP. i.e. "& jobid &" Now when I run this as function, it works but when try to use it Invoke Custom function, it gives error. Please help

[@mrkraymnd]

i followed this from scratch... it's working well in desktop but when i published i receive the following error - any ideas? [Unable to combine data] Section1/Task/Expanded data references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

[@mrkraymnd]

BTW, i am doing api calls against Adobe, not player data :P

[@saadhafeez9171]

Hi I have tried your way. it gives me only 50 rows per request of the id. is there a way to fetch all of the data for each id in same formula or any any other function? need your help I am stuck for days.

[@PowerBIFerryTales]

Hi Saad, are you using the same API I use in the video or something different?

[@saadhafeez9171]

@@PowerBIFerryTales with different API. How to get all the rows with of each id ?

[@PowerBIFerryTales]

@@saadhafeez9171 I will reply to the email you sent.

[@mradzi87]

I followed the same steps, but when I invoke a value I got this as an error Expression.Error: We cannot convert a value of type Record to type Number. Details: Value=[Record] Type=[Type] Do I need to change anything on the table itself ?

[@giftcp82]

This is a good tutorial. For those looking foe pagination please review the list.generate function

[@benmarshall8516]

Brilliant, thank you !

[@jasminef287]

you're a G, ty!

[@NickL-s6b]

So this works great the problem I run into is function queries are not able to be refreshed on the web app. Anyone figured out a work around?

[@arysa6096]

Hello, hope you found a solution. Im leaving this in case someone runs into the same issue kzbin.infoa0FqNLI0VsQ?si=B476mrxBiBpuWjZ0 I was able to "fool" PowerBI Service into stablishing a connection to the domain instead of the API Url using PowerQuery syntax. Good luck!

[@arysa6096]

Hello, hope you found a solution. Im leaving this in case someone runs into the same issue kzbin.infoa0FqNLI0VsQ?si=B476mrxBiBpuWjZ0 I was able to "fool" PowerBI Service into stablishing a connection to the domain instead of the API Url using PowerQuery syntax. Good luck!

[@brunomedeiros8123]

I followed the steps just like in the video, but in the "Invoked function custom" step I got the following error: Expression.Error: The type specified wasn't recognized. Details Table Can you help me?

[@PowerBIFerryTales]

Hey Bruno, it could be that you're using a number field with a text field. Before you do the 'Invoked Custom Function' first first change the data type to text.

[@benmarshall8516]

i think it might be becuase it is in uppercase? I found when I wanted to put the type to date I ran into error when I typed "Date"

[@CHQAQC]

Great video which was easy to follow. A couple of questions. 1) I notice the player photos are included as ######.jpg. What is the picture URL to show the picture? 2) In what table is the team data (points/goals) recorded? thanks in advance!

[@CHQAQC]

The Team Data table is pretty much empty in my case. Do I need to set up relationship to another table or has it been faulty implemented by the guy behind my screen?

[@culebradiver]

👏👏👏👏👏👏 Many thanks!

[@PowerBIFerryTales]

Happy it helped! 🙂

[@LikhitAnuraag]

Just perfect!

[@jourdango2615]

What if the API doesn't tell you how many rows there are to retrieve?

[@PowerBIFerryTales]

I wish I didn't have to say 'it depends', but...it depends. Each API will have a logical starting point, regardless of the data you're working with. Usually there is an endpoint which lists all the unique IDs , and from there you request how much data you want to see, often within a specifiable date range, or range relevant to the data type. It really does depend on the API, but there is usually some form of documentation to steer you in the right direction. Though API documentation is often pretty garbage.

[@jourdango2615]

@@PowerBIFerryTales So the api i am working on now is for a school. The api is called TeachWorks. Based on its documentation, you can only return 80 rows at a time and the way that you traverse is just by page=1 , page = 2 … parameter. But there doesnt seem to be a way to list all IDs or get total count of rows. Right now my implementation is quite crude. First round counts how many rows there are. Second round returns data for countOfRows/80 pages. But it takes really long cuz each endpoint equals 2 rounds :((

[@PowerBIFerryTales]

​@@jourdango2615 Just taking a look at the documentation now. Maybe send me an email with a bit more info such as which precise endpoints you're looking at. My email is on my website. powerbiguy.co

[@jourdango2615]

@@PowerBIFerryTales thank you!!!!!!

[@NatureMajili]

ApI throws error when publish as source combine error. The same file works at desktop.

[@yared7806]

What if I have to iterate since the first API call?. Let me explain... in my case, I have to call a method in the API to get the customers, which divides the info into pages. I don't know how many pages there are, and there is no way I can get all the info at once because the method has a limit of 100 users per call. Fantastic video, by the way; I had no idea this could be done using PowerBI.

[@PowerBIFerryTales]

Hi Yared, the solution that I use for this, is to be honest perhaps not the best, so I will give it a quick check and get back to you. :)

[@darksoca]

Thank you SOOO much

[@anthonyflorianterrapon6575]

Great video, I tried to use it for Web.BrowserContents for HTML and it’s not working. Any ideas ?