How to Use List Generate to Make API calls in Power Query
ฝัง
- เผยแพร่เมื่อ 10 ก.ค. 2024
- Learn how to make API calls by using List.Generate in Power Query. This video takes you through what an API is, how to create series with List.Generate and how to shoot requests to the API until the database is empty.
WRITTEN BLOGPOST
gorilla.bi/power-query/list-g...
Master Functions and Syntax in M
powerquery.how
ABOUT BI GORILLA
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
th-cam.com/users/bigorilla?sub_con...
LET'S CONNECT
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
CHAPTERS
00:00 Introduction
00:35 Explaining the PokeAPI
02:37 From Web for API Call
05:44 Create API Function
07:11 Understanding List.Generate
09:02 List.Generate with Record
11:50 Add API Function to List.Generate
15:39 Condition for Do While logic
MUSIC
Killing Time Kevin MacLeod (incompetech.com)
Licensed under Creative Commons: By Attribution 3.0 License
creativecommons.org/licenses/b...
#api #listgenerate #powerquery
To learn more about the syntax of List.Generate, make sure to check out: powerquery.how/list-generate/
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!
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!
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 !
There are numerous boards floundering for a solution like this. Thank you for the video! It works like a charm.
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/
Thank you so much! Your videos are so helpful for me!
This was great thank you. It helped me create a multi layer paginated outcome generated from an API. Thank you!
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
Awesome! Great lesson with practical example! Thank you!
My pleasure Mariusz :)
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!
Such a great video! Thanks for sharing!
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!
The best one ! Congratulations teacher and thanks for help this poor brazilian guy!!!
Thanks Hugo, glad it works!!
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!
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 ☺️
Very useful video- just getting into API calls- this will help 👍
this is perfect for my project, thank you!
Great video, this is what I'm looking for! Thanks much :)
Thanks, great video, big hug from Colombia
really helpful and clear, thanks!
This is very helpful , i have been searching for such a video for long time ,,,,,,, Thank you man
My pleasure!
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! 🚀🚀
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!
Great video Thanks for your pace and explaining it so easily
Excellent. Thanks for sharing 👍👍
Amazing! Thanks a lot!
thank you so much It really helped me! grateful
You are a legend!!! Thank you!
🚀🚀
This video is very luxurious. Thank you very much. :-)
I like how you describe it, it feels luxurious when it all works too!
Amigo, you are a true mvp
Ok, I don't comment ever, buuuuuuuuuuuuuuut this was amazing. You are a legit MVP
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 :)
Wow. I need to practice practice practice using Power Query
Really helpful, awesome 👌
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 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!
So awesome!
very nice explained
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 !
Great, thanks Subbu!
Thanks!!!
This was very helpfull.
Can we refresh it automtically ?
you are rockstar.
good video I like it
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!
Thank you for such a helpful video. How to get all those list tables into one table to make a report?
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!
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?
Follow starting from 11:49
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
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!
I have done exactly the same in my company and everyone was like this is magic 😀
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.
Sharp content (literally and figuratively)!
🤺 sharp as a blade. How's your power query game these days Michiel. Any exciting tricks down your sleeve?🔥🙏
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!! 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!
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 :)
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
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?
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 ?
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 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?
good video, but the music volume is too high...
I took note nof that. Hope to improve in the next video :)
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?
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?!?!?
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.
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 can't see where your mouse went at 3:19
i got an error.
ERROR: We cannot convert a value of type Table to type List.
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
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