Getting data into Power Query with the twitter search API (how to hack PQ to use OAuth)

Last week I decided to try an interesting experiment. Lets see if I can get data from the Twitter REST API into Power Pivot using Power Query. Power Query already supports two out of three things that are needed for me to import this data:

  1. Import from a web url is already supported
  2. Parsing JSON data is already supported
  3. Logging into the service with OAuth is not supported for a datasource that is not developed by the Power Query team. Unfortunately even though most products use OAuth, no OAuth implementation is the same. Thanks to some hacking with Power Query I managed to work around this.

So lets go take a look at how we can solve this.

The first thing you need to have is a twitter account. That’s easy, next you should think about what kind of data you want to get, there is extensive API documentation that you can look though here to find the one that interest you the most: https://dev.twitter.com/rest/public

I decided to go with the Search API to get a list recent PowerPivot posts: https://dev.twitter.com/rest/public/search. Now the fun begins, first lets get familiar to what the search API is all about. Twitter provides a great API console from APIGee that you can use to play around with the API’s without any code. You can find that here: https://dev.twitter.com/rest/tools/console

I opened the console and selected the search API:

image

Next I signed in using my Twitter username and password. Typed in PowerPivot as the keyword to search for and pressed send. This will execute the API under my credentials:

image

Now this is “all” that  we need to get the data loaded into the data model and Power Query. I open Power Query and use “From Web” and paste in the URL for the API: “https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot

image

This will give you an error message: bad request:

image

If you want to see what is really going on install a sniffer tool like fiddler, there we can see that twitter returns that we aren’t authenticated properly. I will spare you those details but if you want to try it yourself download fiddler here: Fiddler – Official Site.

Now how we can get around this? If the datasource would have been supported by PQ would have gotten a credentials popup or you could choose the right credentials in the Edit Setting dialog you can popup using the button on the screen. Alas these options are not available for us today, we need to start writing M code. So lets pop open the editor, this is the code we have so far:

let
Source = Web.Contents(“https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot”)
in
Source

Very simple right? Now we make twitter aware of who we are by adding authentication to the request. This happens in OAuth by adding headers to the request. Here is where the hacking part starts, we can do this in Power Query by adding some options to the web.contents call as you can read in the help documentation here.

So we change the code by adding the headers that are shown in the dev console above in this fashion:

let

Source = Web.Contents(“https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot&count=100″, [Headers=[#”Authorization”=”OAuth oauth_consumer_key=””YOURKEY””, oauth_nonce=””YOURCODE””, oauth_signature=””YOURSIGNATURE””, oauth_signature_method=””HMAC-SHA1″”, oauth_timestamp=””TIMESTAMP””, oauth_token=””YOURTOKEN””, oauth_version=””1.0″””]]
)
in
Source

 

You can just copy and paste the values from the dev console into Power Query and it will work. Unfortunately it will only work for a small period of time until the token is invalid again, if this was a supported data source in Power Query they would take care of refreshing the token and adding the right values to the header.

Now we see results actually coming back:

image

From here on it is just normal Power Query functionality as it returns JSON format

image

Now I can save and close and analyze the Twitter data inside Excel and Power View. Pretty cool right?

  • Pretty cool Kasper. Just what we needed.

    Hope Power Query vNext will come with a more end-user friendly solution.

  • Very nice. Wonder if a PQ function could handle getting the updated tokens? I may take a look at it.

    • Kasper de Jonge

      Yeah I think that actually might be possible to do that dance every refresh .. I didn’t think about that.

  • Very cool – must try this tonight

  • I tried this, but Power Query continues to request Access Web Content credentials even after editing the Web.Content Headers. I’m using latest Power Query 2.15.xxx version. I just left credentials as Anonymous.

  • Kasper de Jonge

    The code is very specific, I have had that several times as well. Try copy and pasting my code 1:1 and try that. Also make sure you use recent OAuth credentials as they expire.

    If you really want to see what is going on run fiddler to see the details.

  • I’ll give it another go later. BTW, I think your code with the headers is missing a paren after the brackets, and the first code has an extra parens, and the URL is missing quotes around it? … the HTML probably ate them 🙂

  • Kasper de Jonge

    thanks, I fixed it. WordPress made them actual clickable links.

  • I keep receiving
    DataSource.Error: Web.Contents failed to get contents from ‘https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot&count=100’ (400): Bad Request
    Details:
    https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot&count=100
    Did you experience this behaviour?

  • Greg Kramer

    Appreciate your effort here Kasper.

    I got very close but didn’t get this to finally work.

    Will try again with a clear head

    • If you were having problems getting it to fire, it could be as simple as re-typing the quotation marks. try replacing them all manually and see if that works. Caught it as a problem on my side as well…it’s always the simplest thing we didn’t think of! Hope that does the trick.

  • Paul_Turley

    Kasper, thanks for posting this. I also seemed to get really close to a working solution but getting the code sorted out PQ says that the request wasn’t authorized. I’d be interested to see this working.

    • Kurt

      Kasper, I do seem to have the same issue … when executing the request PQ says “The user was not authorized”…in the “Exploring the Twitter API”screen it works fine but for some reason with these settings in PQ it doesn’t …any idea ?

      • chris

        Hi, Kurt, Kasper, did you manager to get over the authorization problem?

    • Bas Land

      I got the same problem here. Did you already found a solution or workaround? I would like to hear.

  • loïc bellinger

    Hello. I have tried this but I got a strange error :”Specified value has invalid CRLF characters” (I’m translating from French). Do you have any idea for me to fix this issue?

    • Alaoui Zeinab

      just make the code in one line and it would work 🙂

  • imtiaz

    Hi, great article, but it never works for me. I always get authentication error 32 when trying to query the API. I replaced all quotation marks, and the code syntax is fine.

  • Greg Kramer

    So are you supposed to do a copy/past job of this?:

    let

    Source = Web.Contents(“https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot&count=100″, [Headers=[#”Authorization”=”OAuth oauth_consumer_key=””YOURKEY””, oauth_nonce=””YOURCODE””, oauth_signature=””YOURSIGNATURE””, oauth_signature_method=””HMAC-SHA1″”, oauth_timestamp=””TIMESTAMP””, oauth_token=””YOURTOKEN””, oauth_version=””1.0″””]]
    )
    in
    Source

    and then plug in your own values? or just copy from the console?

    [#”Authorization”=”OAuth –>not sure if I’m handling this part correctly.

  • BMP

    I found this post very helpful. Thanks for sharing the hack! One thing…how would I go about getting more than 100 records in my results set? Looks like this is a limitation of the Twitter Search API? Any ideas?

  • greensheenaveen

    Expression.Error: The ‘Authorization’ header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Language, Cache-Control, If-Modified-Since, Referer

  • Ram

    I am getting below error while connecting below url

    Details: “Web.Contents failed to get contents from ‘https://api.twitter.com/1.1/search/tweets.json?q=PowerPivot’ (400): Bad Request”

    Please suggest, same error is coming for any search key word

  • Grifffizzle

    Yes, very cool. I have trawled the web trying to find a solution to exactly the same issue I have, but with Salesforce connection to Power Query. I think this might be the solution, but I do not know how or even if it is possible to get the headers I need from Salesforce…?

  • VerySincerely

    I’m going to try this in PowerBI in a second, but I’m curious why OAuth isn’t available as an authentication option. It’s the most widely used API authentication scheme, and I think all we (developers) need is the ability to enter a list of HTTP headers.

  • Chris Koester

    I’ve developed a solution that performs the authentication step within Power Query, so the entire process is in a single query. You can find it here – https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/