Forum Discussion
How to use cursoring or pagination in Power M Query? Twitter API
Hi Anonymous ,
You can use PowerQuery funcion called List.Generate. Here is an information about it - https://www.excelandpowerbi.com/?tag=list-generate. Let me know if this helps and you have question about it.
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups
Hello
as already statet zoloturu List.Generate is the way you can create loops.
Create a function for you Twitter-api-calls and use as parameter the inpagination
Then you can create a List.Generate like this to retreive data based on inpagination
= List.Generate( ()=>
[Result= try GetData(your inpagination) otherwise null, Page= your inpagination],
each [Result]<> null,
each [Result = try GetData([Page]+your inpagination increment) otherwise null, Page= [Page] + your inpagination increment],
each [Result])but you could also create a list, and then apply List.Transform and use the values as parameter for you function
have fun
Jimmy
- Anonymous6 years agoNot applicable
I modified the code like this and it doesnt display me any error, but it still just show me 100 results... I think im really near but I don't know too much about this and I dont really know where the error is at... IF you could help me to modify know i would be very gratefull!! 😁
/* This M script gets an bearer token and performs a tweet search from the Twitter REST API https://dev.twitter.com/oauth/application-only Requires establishing a Twitter application in order to obtain a Consumer Key & Consumer Secret https://apps.twitter.com/ IMPORTANT - The Consumer Key and Consumer secret should be treated as passwords and not distributed */ let // Concatenates the Consumer Key & Consumer Secret and converts to base64 authKey = "Basic " & Binary.ToText(Text.ToBinary("<ConsumerKey>:<ConsumerSecret>"),0), url = "https://api.twitter.com/oauth2/token", // Uses the Twitter POST oauth2/token method to obtain a bearer token GetJson = Web.Contents(url, [ Headers = [#"Authorization"=authKey, #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content = Text.ToBinary("grant_type=client_credentials") ] ), FormatAsJson = Json.Document(GetJson), // Gets token from the Json response AccessToken = FormatAsJson[access_token], AccessTokenHeader = "bearer " & AccessToken, // Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method Pagination=1, ListPagination= List.Generate( ()=> [Result= try GetData(Pagination) otherwise null, Page= Pagination], each [Result]<> null, each [Result = try GetData([Page]+1) otherwise null, Page= [Page] + 1], each [Result]), GetJsonQuery = Web.Contents("https://api.twitter.com/1.1/search/tweets.json?q=Allegient&count=100", [ Headers = [#"Authorization"=AccessTokenHeader] ] ), FormatAsJsonQuery = Json.Document(GetJsonQuery), NavigateToStatuses = FormatAsJsonQuery[statuses], TableFromList = Table.FromList(NavigateToStatuses, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandColumn = Table.ExpandRecordColumn(TableFromList, "Column1", {"metadata", "created_at", "id", "id_str", "text", "source", "truncated", "in_reply_to_status_id", "in_reply_to_status_id_str", "in_reply_to_user_id", "in_reply_to_user_id_str", "in_reply_to_screen_name", "user", "geo", "coordinates", "place", "contributors", "is_quote_status", "retweet_count", "favorite_count", "entities", "favorited", "retweeted", "lang", "possibly_sensitive", "quoted_status_id", "quoted_status_id_str", "quoted_status"}, {"Column1.metadata", "Column1.created_at", "Column1.id", "Column1.id_str", "Column1.text", "Column1.source", "Column1.truncated", "Column1.in_reply_to_status_id", "Column1.in_reply_to_status_id_str", "Column1.in_reply_to_user_id", "Column1.in_reply_to_user_id_str", "Column1.in_reply_to_screen_name", "Column1.user", "Column1.geo", "Column1.coordinates", "Column1.place", "Column1.contributors", "Column1.is_quote_status", "Column1.retweet_count", "Column1.favorite_count", "Column1.entities", "Column1.favorited", "Column1.retweeted", "Column1.lang", "Column1.possibly_sensitive", "Column1.quoted_status_id", "Column1.quoted_status_id_str", "Column1.quoted_status"}) do { in ExpandColumn- zoloturu6 years ago
Memorable Member
Anonymous ,
Can you describe which twitter API request parameters you are going to use in order to implement pagination?
Your request is next
Web.Contents("https://api.twitter.com/1.1/search/tweets.json?q=Allegient&count=100"...But when looping it will return still same 100 values.
As per documentation of this API you can leverage until, since_id and max_id parameters in order to achieve some kind of pagination, but not ideal
https://developer.twitter.com/en/docs/tweets/search/api-reference/get-search-tweets
After we define which request to use we can loop it in PowerQuery.
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer- Anonymous6 years agoNot applicable
Yes, I'm using that documentation parameters, Im searching for tweets containing Alligent
But yes, As you said it loops and return the same 100 values.. How do I Introduce in my code the until, since id and max id?
I need to put it manually in each request or It can be programmed to take the max id as since id in each loop?