To learn more about the syntax of List.Generate, make sure to check out: powerquery.how/list-generate/
@MuesliJuice9 ай бұрын
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!
@subx0ne2 ай бұрын
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!
@seandavies993 ай бұрын
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 !
@barryzukose4036 Жыл бұрын
There are numerous boards floundering for a solution like this. Thank you for the video! It works like a charm.
@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! 🚀🚀
@AjayPrashar2 жыл бұрын
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.
@BIGorilla2 жыл бұрын
Thank you Ajay, I am very glad this video helped out in understanding the logic!
@RenierWessels Жыл бұрын
This was great thank you. It helped me create a multi layer paginated outcome generated from an API. Thank you!
@anuragkat667 ай бұрын
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
@alexanderdejonge178423 күн бұрын
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.
@wonnapee2 жыл бұрын
This s awesome stuff. I really got firm understanding of List.Generate now. GODLY THANKS to you!
@BIGorilla2 жыл бұрын
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/
@HansdeCocq2 жыл бұрын
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!
@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!
@amid77572 жыл бұрын
Thank you so much! Your videos are so helpful for me!
@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!
@hugomcferreira2 жыл бұрын
The best one ! Congratulations teacher and thanks for help this poor brazilian guy!!!
@BIGorilla2 жыл бұрын
Thanks Hugo, glad it works!!
@jonathanmorales32344 ай бұрын
Ok, I don't comment ever, buuuuuuuuuuuuuuut this was amazing. You are a legit MVP
@mnowako2 жыл бұрын
Awesome! Great lesson with practical example! Thank you!
@BIGorilla2 жыл бұрын
My pleasure Mariusz :)
@GusGuitarPD2 жыл бұрын
Such a great video! Thanks for sharing!
@Dave.adriaanse2 жыл бұрын
Love the improvement of your video quality!
@BIGorilla2 жыл бұрын
Thanks Dave 🔥🔥 it's a new camera. And with some tips of a friend, it turned out to be a big improvement ☺️
@oconnorra2 жыл бұрын
You are a legend!!! Thank you!
@BIGorilla Жыл бұрын
🚀🚀
@fajnefajne43392 жыл бұрын
This video is very luxurious. Thank you very much. :-)
@BIGorilla2 жыл бұрын
I like how you describe it, it feels luxurious when it all works too!
@subbunittala20122 жыл бұрын
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!
@Orholam52 жыл бұрын
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!
@diegolozano2397 Жыл бұрын
Thanks, great video, big hug from Colombia
@richardconley53436 ай бұрын
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
@mahmoudomira11512 жыл бұрын
This is very helpful , i have been searching for such a video for long time ,,,,,,, Thank you man
@BIGorilla2 жыл бұрын
My pleasure!
@MichaelBrown-lw9kz11 ай бұрын
Wow. I need to practice practice practice using Power Query
@martyc5674 Жыл бұрын
Very useful video- just getting into API calls- this will help 👍
@astatine008511 ай бұрын
this is perfect for my project, thank you!
@berggrog1 Жыл бұрын
Great video Thanks for your pace and explaining it so easily
@hh_svmhonghe69612 жыл бұрын
This video helps. However, Are you able to create a video for cursor (text.endpoint eg, next page link))? Thanks
@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 :)
@timc75352 жыл бұрын
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?
@BIGorilla2 жыл бұрын
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!
@hukumka26012 жыл бұрын
Thank you for so clear explanation! I have left "pokemon" column in the loop in my query as a tribute to you :)
@BIGorilla2 жыл бұрын
Highly appreciated 😂
@fabiobellegard9830 Жыл бұрын
really helpful and clear, thanks!
@kebincui2 жыл бұрын
Excellent. Thanks for sharing 👍👍
@dvenkateshreddy Жыл бұрын
Great video, this is what I'm looking for! Thanks much :)
@vinodkotiya Жыл бұрын
very nice explained
@kbelinho08 Жыл бұрын
thank you so much It really helped me! grateful
@andonii46 Жыл бұрын
Amigo, you are a true mvp
@orleanCosta Жыл бұрын
So awesome!
@rajuuppara57712 жыл бұрын
Really helpful, awesome 👌
@geetashah269410 ай бұрын
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?
@gregoryoliveira8358 Жыл бұрын
Amazing! Thanks a lot!
@shehana4172 Жыл бұрын
Thanks!!! This was very helpfull. Can we refresh it automtically ?
@Sanatani_adiyogi6 ай бұрын
I have done exactly the same in my company and everyone was like this is magic 😀
@sowjanyag7601 Жыл бұрын
Thank you for such a helpful video. How to get all those list tables into one table to make a report?
@SOEDEMICHIEL2 жыл бұрын
Sharp content (literally and figuratively)!
@BIGorilla2 жыл бұрын
🤺 sharp as a blade. How's your power query game these days Michiel. Any exciting tricks down your sleeve?🔥🙏
@deddle2 жыл бұрын
Thanks! Very good video! Now let's see if i can use this to retrieve tweets from the Twitter API 😁
@deddle2 жыл бұрын
And it worked! Thank you so much!
@FarazShadab9 ай бұрын
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?
@hervelebars2529 ай бұрын
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 ?
@erinfield1943 Жыл бұрын
Follow starting from 11:49
@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!
@sarahkaufman45052 жыл бұрын
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?
@BIGorilla2 жыл бұрын
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
@rudisoft2 жыл бұрын
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.
@veriyoshioka77232 жыл бұрын
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!
@min2naing141 Жыл бұрын
good video I like it
@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
@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
@marcogarzotti55227 ай бұрын
good video, but the music volume is too high...
@BIGorilla7 ай бұрын
I took note nof that. Hope to improve in the next video :)
@fortak20062 жыл бұрын
very useful, and could you mind providing one more tutorial about the setting of request header? thank you
@BIGorilla2 жыл бұрын
Truth be told, I don't know the request header settings are. What are those used for?
@fortak20062 жыл бұрын
@@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
@muhammadovaisjahanzaib65532 жыл бұрын
you are rockstar.
@jamesfarrell68502 жыл бұрын
Very useful and clear. Could you do a video for how to call an API that has a continuation token available?
@BIGorilla2 жыл бұрын
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?
@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?
@bensycamore11 ай бұрын
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?
@tiagocarvalhal45022 жыл бұрын
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.
@ItsWithinYou5 ай бұрын
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?
@tjross79852 жыл бұрын
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?
@eddiedemorizi76522 жыл бұрын
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
@tranducchung56552 жыл бұрын
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!! ^^
@BIGorilla2 жыл бұрын
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 :)
@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?!?!?
@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.
@frederikceglarek Жыл бұрын
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?
@jacoblim863410 ай бұрын
I can't see where your mouse went at 3:19
@saadhafeez91712 жыл бұрын
i got an error. ERROR: We cannot convert a value of type Table to type List.
@MrSpiritUAll2 жыл бұрын
9:57 je zegt curly brackets maar het zijn square brackets
@BIGorilla2 жыл бұрын
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
@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