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!
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
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 !
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.
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.!😍🥰
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.
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 :)
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 !
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!
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?
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
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?
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.
@@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 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 ?
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.
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 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 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?
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
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?
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"
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 :)
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
To learn more about the syntax of List.Generate, make sure to check out: powerquery.how/list-generate/
Is it possible to call 3lakh rows table thru API Calls to Power BI?
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!
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
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!
There are numerous boards floundering for a solution like this. Thank you for the video! It works like a charm.
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 !
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.
As someone new to this, great explanation and really saved me some headache.
Thank you Bean, I'm very happy this helped to wrap your head around things. It's not an easy topic! 🚀🚀
Ok, I don't comment ever, buuuuuuuuuuuuuuut this was amazing. You are a legit MVP
The best one ! Congratulations teacher and thanks for help this poor brazilian guy!!!
Thanks Hugo, glad it works!!
This s awesome stuff. I really got firm understanding of List.Generate now. GODLY THANKS to you!
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.
Thank you Ajay, I am very glad this video helped out in understanding the logic!
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.
thanks for your support!
This was great thank you. It helped me create a multi layer paginated outcome generated from an API. Thank you!
Such a great video! Thanks for sharing!
Hey Rick, not yet used, but i’m impressed of your solution here!!! This one goes in my “specials library”
:)
Thank you Hans, these indeed are special cases. You won't need them on a daily basis, but they work!
Thank you so much! Your videos are so helpful for me!
You are a legend!!! Thank you!
🚀🚀
Love the improvement of your video quality!
Thanks Dave 🔥🔥 it's a new camera. And with some tips of a friend, it turned out to be a big improvement ☺️
Thanks, great video, big hug from Colombia
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.!😍🥰
Happy to hear that - glad to help!
This video is very luxurious. Thank you very much. :-)
I like how you describe it, it feels luxurious when it all works too!
Very useful video- just getting into API calls- this will help 👍
This is very helpful , i have been searching for such a video for long time ,,,,,,, Thank you man
My pleasure!
this is perfect for my project, thank you!
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.
Great video Thanks for your pace and explaining it so easily
Awesome! Great lesson with practical example! Thank you!
My pleasure Mariusz :)
Wow. I need to practice practice practice using Power Query
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!
Creating that function first will make things so much easier, please do so!
Thank you for so clear explanation! I have left "pokemon" column in the loop in my query as a tribute to you :)
Highly appreciated 😂
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 :)
Amigo, you are a true mvp
really helpful and clear, thanks!
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
So awesome!
Great video, this is what I'm looking for! Thanks much :)
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 !
Great, thanks Subbu!
Excellent. Thanks for sharing 👍👍
thank you so much It really helped me! grateful
Amazing! Thanks a lot!
Really helpful, awesome 👌
Thanks! Very good video! Now let's see if i can use this to retrieve tweets from the Twitter API 😁
And it worked! Thank you so much!
This video helps. However, Are you able to create a video for cursor (text.endpoint eg, next page link))? Thanks
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!
very nice explained
Thanks!!!
This was very helpfull.
Can we refresh it automtically ?
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?
Hi cm. You can read some here in the comments: gorilla.bi/power-query/list-generate-api-calls/#comment-5061
Hope it helps!
Thank you for such a helpful video. How to get all those list tables into one table to make a report?
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?
good video I like it
I have done exactly the same in my company and everyone was like this is magic 😀
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
Follow starting from 11:49
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?
you are rockstar.
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.
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!
How can I create a function for both "limit" and offsett? Thanks!
very useful, and could you mind providing one more tutorial about the setting of request header? thank you
Truth be told, I don't know the request header settings are. What are those used for?
@@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
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 ?
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.
Very useful and clear. Could you do a video for how to call an API that has a continuation token available?
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?
Sharp content (literally and figuratively)!
🤺 sharp as a blade. How's your power query game these days Michiel. Any exciting tricks down your sleeve?🔥🙏
good video, but the music volume is too high...
I took note nof that. Hope to improve in the next video :)
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?
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
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 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?
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
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?
You can define a record with multiple arguments. For example, look here: gorilla.bi/power-query/list-generate/#fibonacci-sequence
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"
I managed to figure it out. Needed to add RelativePath to the code so that it could be used dynamically (thumbsup)
Happy to hear that, thanks for reporting back Adam.
I can't see where your mouse went at 3:19
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 :)
9:57 je zegt curly brackets maar het zijn square brackets
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
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?
i got an error.
ERROR: We cannot convert a value of type Table to type List.
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