You might be surprised to learn that you can post to twitter from Power Query. After all, the primary use for Power Query is obtaining and transforming data. While this is true, Power Query also offers the ability to perform an HTTP POST, which means that we can submit data via the web. My first blog post titled “Get Data from Twitter API with Power Query” involved an HTTP POST in order to obtain a token from the Twitter API. In this post I’ll show how you can Tweet from Power Query with some help from Temboo.
So what is Temboo? Temboo is a service that allows us to offload a massive amount of effort involving authentication with the Twitter API. The process goes like this:
- An HTTP POST is performed from Power Query to submit our Tweet and Twitter application keys to Temboo.
- Temboo authenticates with the Twitter API on our behalf and relays our Tweet.
While it would be nice to be able to Tweet from Power Query alone, I am not exaggerating when I say that it would be a massive effort. For starters, one part of the authentication process involves calculating a HMAC-SHA1 hash, for which Power Query does not offer a function. The entire HMAC-SHA1 algorithm would have to be reproduced from scratch using M code, which would not be a trivial task! If you want to learn more about authentication with the Twitter API, you can read their instructions for generating an OAuth 1.0a HMAC-SHA1 signature. Most programming languages offer libraries that do this authentication for you, so not every developer has to duplicate this tedious work. There are no existing Twitter authentication solutions for Power Query, and this is where Temboo comes to the rescue. You could think of Temboo as a cross-platform API library in the cloud.
Create Twitter Application and Temboo Account
Before we get to Power Query, you must first establish a Twitter application and an account with Temboo. I’m not going to describe creating a Twitter application in detail, as it’s pretty straightforward and it’s easy to find instructions on the web if you need them. Just be sure that you set your application permissions to Read and write, so that the app has sufficient permissions to update your Twitter status (Tweet). Creating a Temboo account is also very easy. You’ll get a free trial of the Enterprise plan, which will convert to the Free plan after 30 days. You can view the details of the plan levels here. One thing that I’ll mention about the free plan is that the application key has to be regenerated on a monthly basis.
Temboo Choreo
Temboo refers to their API services as Choreos (Short for choreographies). We’ll be using the Twitter status update choreo. The screenshot below shows where you can input your Twitter tokens and keys to facilitate generating code for your application. Since I’ve authored the Power Query M code already, you don’t need to input anything here.
As I mentioned, you don’t need to fill out the inputs in Temboo. However, if you want to know my development path, I did fill out the inputs the first time in order to generate the JSON content in Power Query. What I did was fill out the inputs, selected the cURL code, and then ported it to M. You can use presets in Temboo to make your client code remarkably short, but these presets expire monthly with the free Temboo plan, so I did not use one.
Power Query Code
With the Twitter application and Temboo account setup, we’re ready to look at the Power Query code. Replace all values between <angle brackets> with your own credentials and keys (Angle brackets should not be in the final values).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
let status = "Status update from Power Query!", tembooUsername = "<temboo_username>", url = "https://" & tembooUsername & ".temboolive.com/temboo-api/1.0/choreos/Library/Twitter/Tweets/StatusesUpdate", appName = "<temboo_app_name>", appKey = "<temboo_app_key", consumerKey = "<twitter_consumer_key>", accessToken = "<twitter_access_token>", consumerSecret = "<twitter_consumer_secret>", accessTokenSecret = "<twitter_access_token_secret>", // Base64 encode Temboo credentials and establish basic authentication header value credentials = "Basic " & Binary.ToText(Text.ToBinary(appName & ":" & appKey),0), // json submitted to Temboo using an HTTP POST jsonContent = "{""inputs"": [ {""name"":""StatusUpdate"", ""value"":""" & status & """}, {""name"":""ConsumerKey"", ""value"":""" & consumerKey & """}, {""name"":""AccessToken"", ""value"":""" & accessToken & """}, {""name"":""ConsumerSecret"", ""value"":""" & consumerSecret & """}, {""name"":""AccessTokenSecret"", ""value"":""" & accessTokenSecret & """} ] }", StatusUpdate = Web.Contents(url, [ Headers = [#"Authorization" = credentials, #"x-temboo-domain" = "/" & tembooUsername & "/master", #"Content-Type" = "application/json", #"Accept" = "application/json"], Content = Text.ToBinary(jsonContent) ] ), jsonResponse = Json.Document(StatusUpdate) in jsonResponse |
When the query runs for the first time, you should see a pop up asking you how you would like to authenticate. Select anonymous access and select the root of the Temboo URL, which should be the default.
Press Connect, and if all goes well you should receive a response from Temboo in JSON. Now head over to Twitter to see your Tweet!
What About Power BI?
Unfortunately I have not been able to tweet from a query that has been published to Power BI. The query will run successfully from Power Query and Power BI Desktop, but I get an authentication error when trying to run it from the Power BI site. There are a couple of people on the Power BI forum who have claimed to have gotten around this problem, but I have not had a chance to try their solution yet. If anyone gets this working, please let me know!
this looks interesting on the surface with PBI connecting back to its data source…..will try it out and see if it works….Thanks for sharing such enhanced M/PQL…..amazing.
Thank you, David. Good luck!