This is such a well structured tutorial. It was exactly what I needed to finally get my API calls working after many failed attempts to automate a tedious data extract for a regular report I have to do. You literally just saved me hours of manual effort every month. Thank you so much!
Your video and text were an ABSOLUTE starting point for me. But it didn't fully resolve the issues that I had. First point that I didn't comprehend was that the condition line ALSO applies to what is retrieved from the initial line and if it ALSO fails the condition, the records are not retrieved. I utilized a List.Count() to determine if records from the API were > then the known limit of 50. I then used an if then else in the next function to evaluate if the records were less than or equal to 50 AND the cursor value was equal to null to determine the next process. My API call(s) had to include 2 different functions. One if the cursor was null and one if the cursor had a string value. This has broken open a totally different world of reporting for me and the organization that i work with. It is very exciting. BTW, the API I am working with is a GraphQL API returning a JSON value. I am getting better at Power Query and look forward to making my queries and reporting more efficient.
You have made the best reference video I have seen for making API calls with Power BI. It is so simple but is frequently taught in an overly complex way. Thank you for this!
Best thing that I have found for paginated/offset API queries using Power Query !! Excellent tutorial ! Thank you !!! Works a dream for my scenario. I was expecting you to use the object count returned by the API to decide how many calls to make, rather than looking for an empty list. But it works !
After Searching for so much time and not finding any Easy Tutorial for API calls, I finally found something that is so easy to understand and use. Big Thumbs Up and Subscribed Brother, Keep Growing
Hi, not only an absolute time saver for me, but also really well explained and structured! I was in need to call an API, with 2 variables, YEAR (5 year values) and OBJECTNO (21 objects), so 105 API calls, this saved me so much time. Unfortunately, I have a time constrain, I am only allowed to call the API once every minute, and still struggling to get that into the function.
Glad to hear that Alex. Also make sure to check out the written blogpost. It has several other examples you may find helpful! gorilla.bi/power-query/list-generate/
This example is very well explained and applicable to my needs. I started to tear up at the quality of the explanation as I have been trying to understand this for some time. It was a very emotional video for me.
EXCELLENT content. I was laughing because of the Pokémon example; it is easier to pay attention that way. Also, I loved how you explained step by step, making it really easy to follow along. Thank you!! You have my like =D.
I was struggling to extract data from a Rest API from over a week.! This video really helped me to use List functions and get all the records.! Thank you bro.!😍🥰
This great tutorial and learned a lot. Only issue is I have a table with 90k rows and it wants to refresh the whole thing everytime. It's be nice to have a tutorial on how to only add when a new record is found
just awesome content ..thank you very much from india ...was having a headache having come from java ..this mquery doesn't support variable re-assignment ..whcih was a headache for me ..you have shown a way for me now !
Thank you for this video. Its really helpful. I have used this in my power bi and I am able to retrieve the data. But how to create a gateway using API with this?
This is great, It is very usefull, I had multiple project that would require something like that, Thank you, my future project will have the List.Generate :)
Really excellent as power bi doesnt have a loop function, i have a same requirement, but in my web.contents I have to content parameter where i have to input vql search query which also goes in a string. One question, can we use python script to call and loop api call and is it performance effective?
Thanks for the video -really clear and helpful. My question is in your example you have 12 'Records' in your 'List' - how do you load them all into a single table in Excel?
That's a good question Tim. When you have a list of records, you can go to Transform (tab) -> Click on To Table. And then expand record columns after by using the arrows in the top. Hope that helps!
Hello Excellent tutorial on how to loop. Compact and efficient. I have still a problem, the query is not detected as a data source by PowerBi. The conséquence is that I can't make planification of update when the report is published on the server. I thnin the problem is the same with report server and cloud version. Have you some tips to make API Call in loop but keep it as a DataSource for PowerBi ?
ok i get this video to a certain extent great explinations dont get me wrong, but is there a way to do multiple variables? for instance the api i use uses a &from &to in the url is there away to offset both by 84600 to search the next day
Very helpful. However, did you tried publish that and refresh from the power bi online? I think you may get an error with formula.firewall. Let us know your solution for that.
@@BIGorilla I believe I mix httprequest used in VBA and the web api, In httprequest, the web will return the dynamic token to the user for login after setting the request header. I also see the setting of request header in power query, but not too much in online resource, may be not support in power query yet. thank you
Hello! This was really helpful for me, as I have been trying to find a way to create a loop getting lots of data from an API. Unfortunately it runs pretty slowly for me. I was wondering if you have any tips on how to improve performance?
Hey Sarah, Glad to hear you found a use for it. I haven't tried this on many API's so it's difficult to tell. Perhaps if you share a bit more on which API you're calling and what code you use, some people may be able to help. I'll be off for vacation coming days myself. Best, Rick
Here's what you want to have a look into: www.thebiccountant.com/speedperformance-aspects/ List.Buffer or Table.Buffer will make a significant performance improvement for you. Do not use docs.microsoft.com/en-us/power-query/helperfunctions#tablegeneratebypage since this (even though provided by MS) tends to be rather slow.
Great video, trying to use it with cursor in api.. where the next value is a "random text" generated by the previous request. Not a number offset like in this video. Hoping ill figure it out based on your video or great if anyone has any input.
If it's like the API I am using where the random generated page number is sent back as a response header, you might be SOL since PowerBI hides all the custom response headers like next page number. Unfortunately the only way to do this is by building a custom connector
What if your API has a bearer token? I have to use a username and password in a post call to retrieve a bearer token code then use that code in the other API calls. How would that work?
I got my use case to work, but the problem is when I publish to the service, I can't schedule a refresh. It says "You can't schedule refresh for this dataset because the following data sources currently don't support refresh". Any ideas to workaround that?
I need to connect to API. For connection, there are 2 parameters - client id and password. Once enter the values of these 2 parameters, we get a token to access the api. This token is valid for 60 minutes so if the report is going to refresh again after 60 mins, it has to run this process again and generate a new security token. After accessing api, I need to input values from a column (from an existing table in power bi report) and api will give me rate value (in integer format) for each of these values. I need to save these rate values in a new column in an existing power bi table. Can you please provide guideline on how to accomplish this?
I did create a function and made API calls using List.Generate. But I receive a warning saying, "Some data sources may not be listed because of hand-authored queries", with this I couldn't refresh the dataset in Power BI service, can you help?
This is really great... but I am having an issue whilst putting this onto a Power BI service. When it is uploaded, it is giving this error. Any help would be appreciated, because I am not able to figure out how to fix it. It works, really well on the desktop... "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed"
How do you use multiple ()=> in your function? It looks like the URL associated with my API only allows for the use of one =“ & Number.ToText. Any ideas?
Would you show how to handle the pagination in Stripe API which, strangely, uses an ending_before, starting_after (object ID), and has_more (True/False) parameters. Why couldn't they put in page numbers like any other API?!?!?
Hello sir! thank you so much for your great video. Pls kindly help : What if the list contain only text value (not numeric), how can we set the NEXT argument in the List.Generate() function? Thank you once again and have a nice week ahead!! ^^
Hey Tran, At 16:31 you can find that I use the List.IsEmpty function. It doesn't matter if it refers to a list of dates, text or numbers, that function works regardless of the contents. You can use it :)
Hi I was wondering if you would be able to help me with a custom api I got can only display 10,000 results per pagination offset. And its offset can go up to 40,000
Hi Eddie, where exactly are you currently struggling? You could make a request that takes 10000 results, then for the next request take the next 10.000. List.Generate can do such a thing :)
@@BIGorilla The issue is that the website's api only goes up to 40,000 results even though there could be way more data than that in one day and it seems there is an overlap in data when I do any offset
Additional idea: How to use List.Generate to loop over an OData.Feed? I am struggling using the explained solution when using the Odata.Feed Function instead of the Web.Contents Function. Maybe someone reading this knows an answer?
What a great video. I have a problem with ETL, can you help me, I will explain the following example: I have the "Qtd" column and I need to add the "Div" column, which counts the number of "Zero" values under each number. Example: The number 15 has two zeros below it, so it counts those two zeros + 1, until it reaches 10 and repeats the process and so on. I hope you read it and Success for your Channel. Qtd Div 15 3 0 3 0 3 10 null 5 2 0 2 50 5 0 5 0 5 0 5 0 5
