How to Use List Generate to Make API calls in Power Query

[@BIGorilla]

To learn more about the syntax of List.Generate, make sure to check out: powerquery.how/list-generate/

[@AK-ij9il]

Is it possible to call 3lakh rows table thru API Calls to Power BI?

[@MuesliJuice]

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!

[@barryzukose4036]

There are numerous boards floundering for a solution like this. Thank you for the video! It works like a charm.

[@subx0ne]

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!

[@seandavies99]

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 !

[@anuragkat66]

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

[@wonnapee]

This s awesome stuff. I really got firm understanding of List.Generate now. GODLY THANKS to you!

[@BIGorilla]

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/

[@hugomcferreira]

The best one ! Congratulations teacher and thanks for help this poor brazilian guy!!!

[@BIGorilla]

Thanks Hugo, glad it works!!

[@bean4081]

As someone new to this, great explanation and really saved me some headache.

[@BIGorilla]

Thank you Bean, I'm very happy this helped to wrap your head around things. It's not an easy topic! 🚀🚀

[@amid7757]

Thank you so much! Your videos are so helpful for me!

[@yared7806]

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.

[@BIGorilla]

thanks for your support!

[@Dave.adriaanse]

Love the improvement of your video quality!

[@BIGorilla]

Thanks Dave 🔥🔥 it's a new camera. And with some tips of a friend, it turned out to be a big improvement ☺️

[@GusGuitarPD]

Such a great video! Thanks for sharing!

[@fajnefajne4339]

This video is very luxurious. Thank you very much. :-)

[@BIGorilla]

I like how you describe it, it feels luxurious when it all works too!

[@oconnorra]

You are a legend!!! Thank you!

[@BIGorilla]

🚀🚀

[@diegolozano2397]

Thanks, great video, big hug from Colombia

[@RenierWessels]

This was great thank you. It helped me create a multi layer paginated outcome generated from an API. Thank you!

[@jackylow86]

Awesome. Although too technical for me now but I think this will be useful especially when doing monthly reporting and probably with checking conditions as well. Thanks.

[@mnowako]

Awesome! Great lesson with practical example! Thank you!

[@BIGorilla]

My pleasure Mariusz :)

[@mahmoudomira1151]

This is very helpful , i have been searching for such a video for long time ,,,,,,, Thank you man

[@BIGorilla]

My pleasure!

[@michellebrayton3570]

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.

[@HansdeCocq]

Hey Rick, not yet used, but i’m impressed of your solution here!!! This one goes in my “specials library” :)

[@BIGorilla]

Thank you Hans, these indeed are special cases. You won't need them on a daily basis, but they work!

[@jonathanmorales3234]

Ok, I don't comment ever, buuuuuuuuuuuuuuut this was amazing. You are a legit MVP

[@astatine0085]

this is perfect for my project, thank you!

[@berggrog1]

Great video Thanks for your pace and explaining it so easily

[@martyc5674]

Very useful video- just getting into API calls- this will help 👍

[@AjayPrashar]

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.

[@BIGorilla]

Thank you Ajay, I am very glad this video helped out in understanding the logic!

[@fabiobellegard9830]

really helpful and clear, thanks!

[@andonii46]

Amigo, you are a true mvp

[@ExcelInstructor]

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 :)

[@dvenkateshreddy]

Great video, this is what I'm looking for! Thanks much :)

[@Orholam5]

Thank you, this was super helpful! I didn't understand I had to create a function first, then call it within my Power Query table!

[@BIGorilla]

Creating that function first will make things so much easier, please do so!

[@kbelinho08]

thank you so much It really helped me! grateful

[@DestinationRides]

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.!😍🥰

[@BIGorilla]

Happy to hear that - glad to help!

[@MichaelBrown-lw9kz]

Wow. I need to practice practice practice using Power Query

[@kebincui]

Excellent. Thanks for sharing 👍👍

[@subbunittala2012]

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 !

[@BIGorilla]

Great, thanks Subbu!

[@rajuuppara5771]

Really helpful, awesome 👌

[@gregoryoliveira8358]

Amazing! Thanks a lot!

[@hukumka2601]

Thank you for so clear explanation! I have left "pokemon" column in the loop in my query as a tribute to you :)

[@BIGorilla]

Highly appreciated 😂

[@vinodkotiya]

very nice explained

[@hh_svmhonghe6961]

This video helps. However, Are you able to create a video for cursor (text.endpoint eg, next page link))? Thanks

[@alexanderdejonge1784]

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.

[@shehana4172]

Thanks!!! This was very helpfull. Can we refresh it automtically ?

[@orleanCosta]

So awesome!

[@richardconley5343]

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

[@timc7535]

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?

[@BIGorilla]

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!

[@erinfield1943]

Follow starting from 11:49

[@sowjanyag7601]

Thank you for such a helpful video. How to get all those list tables into one table to make a report?

[@cmd445]

thanks!! very clear! I would like to make API calls but the one I want comes with a basic authentification. How can I handle this in power query?

[@BIGorilla]

Hi cm. You can read some here in the comments: gorilla.bi/power-query/list-generate-api-calls/#comment-5061 Hope it helps!

[@aaronbennett5552]

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

[@marcogarzotti5522]

good video, but the music volume is too high...

[@BIGorilla]

I took note nof that. Hope to improve in the next video :)

[@min2naing141]

good video I like it

[@muhammadovaisjahanzaib6553]

you are rockstar.

[@deddle]

Thanks! Very good video! Now let's see if i can use this to retrieve tweets from the Twitter API 😁

[@deddle]

And it worked! Thank you so much!

[@geetashah2694]

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?

[@FarazShadab]

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?

[@fortak2006]

very useful, and could you mind providing one more tutorial about the setting of request header? thank you

[@BIGorilla]

Truth be told, I don't know the request header settings are. What are those used for?

[@fortak2006]

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

[@SOEDEMICHIEL]

Sharp content (literally and figuratively)!

[@BIGorilla]

🤺 sharp as a blade. How's your power query game these days Michiel. Any exciting tricks down your sleeve?🔥🙏

[@sarahkaufman4505]

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?

[@BIGorilla]

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

[@rudisoft]

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.

[@veriyoshioka7723]

Hi from the future :) This video was VERY helpful - I had the same problem as Sarah - the video + the response to her question solved it! Thank you!

[@haintuvn]

How can I create a function for both "limit" and offsett? Thanks!

[@jamesfarrell6850]

Very useful and clear. Could you do a video for how to call an API that has a continuation token available?

[@BIGorilla]

Hey James, I'd love to try that, yet have to admit I don't have any experience with it. Do you have an API available that I could try it on?

[@jacoblim8634]

I can't see where your mouse went at 3:19

[@tiagocarvalhal4502]

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.

[@Remi-ni5uf]

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.

[@gnarkilleptic4871]

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

[@adamsabourin9416]

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?

[@hervelebars252]

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 ?

[@ItsWithinYou]

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?

[@bensycamore]

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?

[@ramviru6112]

Great video. But how to combine all the 12 records into one so that we could load all the 1118 records together?. Thanks in advance

[@eddiedemorizi7652]

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

[@BIGorilla]

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 :)

[@eddiedemorizi7652]

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

[@tjross7985]

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?

[@BIGorilla]

You can define a record with multiple arguments. For example, look here: gorilla.bi/power-query/list-generate/#fibonacci-sequence

[@dvenkateshreddy]

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?

[@adamtrades]

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"

[@adamtrades]

I managed to figure it out. Needed to add RelativePath to the code so that it could be used dynamically (thumbsup)

[@BIGorilla]

Happy to hear that, thanks for reporting back Adam.

[@tranducchung5655]

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!! ^^

[@BIGorilla]

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 :)

[@MrSpiritUAll]

9:57 je zegt curly brackets maar het zijn square brackets

[@BIGorilla]

Dank je, scherp gehoord. Ik hoorde het zelf ook al na de opname, maar dacht dat ik er wel mee weg kwam ☺️ thanks voor de tip

[@seang2012]

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

[@saadhafeez9171]

i got an error. ERROR: We cannot convert a value of type Table to type List.

[@fpchd]

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?

[@Sanatani_adiyogi]

I have done exactly the same in my company and everyone was like this is magic 😀

[@Conta-Data]

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