How to use cursoring or pagination in Power M Query? Twitter API

[Anonymous]

Hi 

Im using a twitter api to see twits but they restricted the request to a maximum of 100 twits,

and in case we need more they say we can use pagination and cursoring.

Im using this code trying to use cursoring but I dont really know how to loop to get the information well.

My code works well without trying to do one of this techniques, I underlined the loop im trying to reach.

/*
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
cursor = -1
 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 { 
url_with_corsor=GetJsonQuery + "&cursor=" + cursor
response_dictionary= perform_http_get_request_for_url( url_with_cursor )
cursor = response_dictionary[ 'next_cursor' ] } while ( cursor != 0 )
in ExpandColumn

 I dont know if my mistake is only one or if I have many, what modification i need to do?

I will be very grateful if someone know..

[zoloturu]

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

[Jimmy801]

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 

[Anonymous]

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

[zoloturu]

@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

[Anonymous]

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?

[Anonymous]

Hi 

I am facing the same issue.

Have you found the solution to this problem?

[dax]

Hi rubengzz7, 

I am not familiar with the loop function, so I suggest that you could try to @ImkeF , who is professional in M code for more suggestions.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.