How to do Pagination on a REST API in Power Query (Part 1)
Вставка
- Опубліковано 31 лип 2024
- In today's video, I am going to show you how to implement pagination when the number of pages or records to be returned are known.
Chapters:
00:00 Intro
00:30 Not all APIs have the same structure
01:10 Get data from World Bank API
01:45 How pagination works on the API
02:30 Hardcode the number of pages
04:50 Dynamically feed the number of pages
06:06 Explain List.Numbers
08:20 Unknow number of records?
Done!
Here you can download the power bi file used in the video:
Go to:curbal.com/donwload-center
Click on community downloads and
Get download number 67.
World bank api: datahelpdesk.worldbank.org/kn...
List.Numbers: docs.microsoft.com/en-us/powe...
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
If you dont know the number of pages, use this method:
ua-cam.com/video/05yhwnuCjRw/v-deo.html&ab_channel=Curbal
Thank you Corbal. This worked wonderfully for an api call that has variable number of pages (10-100).
Voting for sooner Part 2! Thanks, Ruth!
Sooner will be :)
Applying as well, trying to figure this problem out as we speak
It is out already:
m.ua-cam.com/video/05yhwnuCjRw/v-deo.html
I've been wondering how to do this for ages, this is amazing. I've implemented it today for a client and it's worked perfectly - Thanks!
🎉🎉
Wanted to drop a line and say thank you! Can't tell you how much help this was. Thank you again!
Thanks! 😊
excellent video, knowing how to get information from api is really a great help. This video deserves so much more likes.
As always, a great video with good content! Waiting for the second video! :)
I hear you guys, will do it maybe next week already
Thank you so much ! Exactly what I needed !
Thank you so much! I just got dropped into the deep end of the reporting pool at work, and this was an exceptionally helpful video!
Glad to hear!!!
Thank you so much, wonderful explanation !!
Love this! Making everything dynamic is exciting!
Hi Celia 👋
@@CurbalENhi, Ruth! looking forward for part 2. ;-)
Will do :)
This video is a lifesaver! Thank you so much!!!
🎉🎉🎉
This video is very luxurious. Thank you very much. :-)
thank you for this video! very good explanation !
Yes please Ruth. Would love to see that second video. Thanks for sharing :) Great practical topic
Fun to make too ;)
@@CurbalEN first time with musical interlude?
Nope, done a few times before, was it nice ?
@@CurbalEN I missed some videos?! Yup - I’ll need to slow frame my way through. Will give it a go later this week
I post too many 😂
yes please - part 2 sounds v interesting
It is similar, but even more powerful:)
You are the absolute GOAT
☺️
Great video! 2nd video please!
great video,
currently trying to call all available invoice pages from Xero(accounting tool). can't wait for next part.
Yes, I will try to do it next week!
I realize I'm a couple weeks late but I would love to see part 2!
Will do soon!
Thanks!
Yes please do part 2
Noted!
Cool .. very good idea 👍👍
Thanks
Buen vídeo!!
Gracias !
Hey Loved the video. Making it really easy to learn the stuff. I'd really love to get the second part of the video.
The thing is that my company is usking JIRA and their API does not use this page nr system as you are showing and the table i need to process has 1150 rows, but jira is limiting it to 50 outputs per request. So as a result I think there is tone of people (many jira users) who'd love to get this second part as much as I do.
I have the same issue in Pipedrive API, Did you solved it? Would be very helpful!!
Here it is:
m.ua-cam.com/video/05yhwnuCjRw/v-deo.html
Here it is:
m.ua-cam.com/video/05yhwnuCjRw/v-deo.html
Hello, I get the message "Some data sources may not be listed because of hand-authored queries" when I open the data source settings. Any idea how I could fix this? I think this appears when I dynammically loop the query based on de column1 list
I've had opportunity to do both this and another one where I don't know the amount of pages beforehand, but in both cases, my main issue is that it won't refresh in the service. I'd love a video on that if you have a solution. As always thank you for your amazing content!
I have the exact same problem! I get to create the report but when I publish and try to schedule a refresh I get an errormessage
Try this:
m.ua-cam.com/video/fstsQMZiHME/v-deo.html
Hi there,
I was wondering how you could paginate an API with x pages and where the page ends with Next, mine keeps looping with this method, so I think my example needs another fix right?
Could you help me with that?
Hi, I humble request you for getting suggestion how to import data in power bi from Apache druid . there is not direct connectivity option in power bi.
Hi Madam. I just want to know is it possible in power query to get the data of all duplicates in a single row.
I'm running into a problem with the limits on the number of API calls I can maken (400 max per 5 minutes). Do you have any advice how I could solve this? Is there a way to pause the API calls?
Thanks for all your videos Ruth. They helped numerous times already.
great stuff! one question: since we have the dynamic number of pages in query 'country' do we need 'Query1' any more?
No you don’t need it anymore :)
Hello
Long time viewer, first time caller, keep up the good work
I have had a problem with this that I have encountered before using API but havent been able to solve, it is the dreaded "Formula.Firewall: Query 'Query2' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination" error message
There are examples online but none of them use Web.Contents, can you show me how to solve this?
How can I use Dataflows for this task? I always get a dynamic consult error when I try this trick.
waiting for part-2
Will do soon!
Hey Ruth, Thank you for the tips. I couldn't try as the API isn't working (maybe because of firewall...) however i was wondering if you could schedule refresh in the Web Service. Often it gets the error that Dynamic queries are not supported (as least in Pro version without premium)
You can if use RelativePath and Queries:
m.ua-cam.com/video/fstsQMZiHME/v-deo.html
@@CurbalEN I need to get deeper into that... Thanks a lot Ruth, as always :)
My pleasure!
How can I import data from JIRA server using rest Api.. please help
I have an API that provides a next page key and count of records but says nothing about number of pages. How do I treat this?
Coming up on how to fix unknown number of pages, stay tuned
is this solution available?
Yes:
m.ua-cam.com/video/05yhwnuCjRw/v-deo.html
Great Video!
Can you also do a tutorial to create admin views? (Workspaces, user access, reports, datasets, etc) using REST APIs that refreshes dynamically? (Without having to hardcore your password in the query editor). TIA.
You mean Power BI rest api?
@@CurbalEN Yes please. Powerbi APIs. I am trying to create report from the thousands of workspaces that we have, which can help us manage the content, access/users, datasets etc
With the time I have available at the moment, it is not on my priority list I am afraid. Working with APIs takes long time :(
@@CurbalEN i understand. Thank you for replying.
Hey Ruth, great timing :) because I have problem with that :) And I have a question for You or subscribers: will this solution work on PowerBI Server?
Power BI server or service? It works on the service
@@CurbalEN I mean PowerBI Report Server?
I want to schedule refreshing on PowerBI Report Server and I get this message: "Scheduled Data Refresh is not available for reports. The report does not have any external data connections defined or is using an unsupported data source."
Oh, the on-prem one. It should work, try it.
I take it back:
Report server does not allow dynamic data sources:
Your report contains dynamic data sources. For example, data from the report is used to dynamicaly build the name of a server, the name of a database, or a URL address to fetch contents. Only data sources that are detected when the report was uploaded can be refreshed.
Sooner🙂
😂
NOTE as of 10/5/22: This approach only works in PBI desktop and wont work once published to PBI Online. Refer to "dynamic data source" error.
Dynamic data source error refers to how the url is built not the list.numbers approach
@@CurbalEN fair - would love to see how to fix the "data source error" :D
This might help:
m.ua-cam.com/video/fstsQMZiHME/v-deo.html
you are not showing the whole page you have in powerbi and that makes it very hard to follow you.
Sorry, my editing skills are not the best at times :(
Or you could use ?per_page=9999999 to obtain all the records in one result :)
Oddly enough, you actually can do it with this API. Never seen it before 🤷♀️
Love you so much, because I was lost and do not know what to do once the high speed of the video began cause there are some steps missing. I guess I need a brake from the computer.