I had seen Data Explorer for the first time at the PASS Summit last year, but was not fully aware of its capabilities until the recent PASS BA Conference. I saw demos of how it can load web data into Excel. I thought Data Explorer was more of a cloud BI solution, but now understand that its a very powerful query engine/data transformation tool. You can import data from many sources like you can PowerPivot, but what's fascinating is its ability to load web data from static html pages, tables on web pages, web services, etc. I had a need to search recent activity on Twitter and decided to test drive Data Explorer in Excel.Twitter's Search API returns JSON formatted data, and I heard multiple times that Data Explorer can handle JSON. To start, here's a sample url string you can send to Twitter anonymously using Version 1 of the Search API:
Here's an explanation of the parameters used in this URL string:
- q: The query text. See Using the Twitter Search API for examples and operators.
- rpp: The number of results returned. We're limited to 100 here, but can get more with the REST API.
- until: Tweet date is less than this date formatted as YYYY-MM-DD.
Now we're ready.
In the Data Explorer ribbon, choose the button to retrieve data from Web.
Enter the Twitter Search URL string above in the form and select OK.
This will open a Data Explorer Query window with a table of information about the query results. Click on the word "List" and you'll get a list of records. If you expand the Steps dialog on the right, you'll notice that this added a step in your data shaping process. Pretty cool, right?
Right click on the header of this list and select the To Table option. This convert this list object to a table. If you don't see any records by this point, you'll have to change the parameters of the URL string until you see some records returned. Get familiar with the limitations of Twitter's v1 Search API here.
Accept the list of defaults in the To Table dialog. Click OK.
The resulting table is very similar to the list, but with a very important exception. In the header of the table next to the word "Column" is a small two-arrow icon. Click this little button and it will automatically read the columns defined in the JSON file and give you the option to choose the ones you want to bring into your query. Leave them all selected for now. You can always remove columns later or by modifying the code at this Step in your data shaping process.
Click OK on the columns you'd like to expand and you'll finally get columns of all the tweets.
Now you can rename columns, change data types, or hide columns. OR, you can just copy the code below in the formula window and click refresh.
let
Source = Json.Document(Web.Contents("http://search.twitter.com/search.json?q=passbac&rpp=100&until=2013-04-21")),
results = Source[results],
TableFromList = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangedType = Table.TransformColumns(TableFromList,{},Value.FromText),
#"Expand Column1" = Table.ExpandRecordColumn(ChangedType, "Column1", {"created_at", "from_user", "from_user_name", "geo", "iso_language_code", "text"}, {"CreatedDate", "FromUser", "FromUsername", "Geo", "ISOLanguageCode", "Text"}),
ChangedType1 = Table.TransformColumns(#"Expand Column1",{{"CreatedDate", DateTime.From}})
in
ChangedType1
Click Done on the query window and the data will be imported into Excel. Yes!Now let's parameterize this query so that it's easy to update the search results from Excel instead of repeatedly opening Data Explorer to change the URL. Originally, I followed much of Chris Webb's post on how to do this a parameterized query string in the formula, but for this post, I found it easier building my Twitter Search URL string with an Excel formula. This can handle empty parameters a little better and can encode the "q" parameter text string using Excel's ENCODEURL function as Chris' post suggests you do.
Add another couple tables in another worksheet named Parameters: one to list each parameter's value and one for the Search URL string. It's nice to add a description of each parameter as shown below so you can remember each parameter's definition in the string. Then in the Search URL string table, build the URL with the following formula:
=CONCATENATE("https://search.twitter.com/search.json?q=",ENCODEURL(B2),IF(ISBLANK(B3),"",CONCATENATE("&rpp=",ENCODEURL(B3))),IF(ISBLANK(B4),"",CONCATENATE("&since=",ENCODEURL(B4))))
Now go back to the sheet with the Data Explorer query, and you can either click on the Filter & Shape button in the Query tab of the Table Tools ribbon, or click on the Filter & Shape button in the Query Settings dialog at right that appears when you bring up this worksheet.
let
Source = Json.Document(Web.Contents(Excel.CurrentWorkbook(){[Name="SearchString"]}[Content]{0}[SearchString])),
results = Source[results],
TableFromList = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangedType = Table.TransformColumns(TableFromList,{},Value.FromText),
#"Expand Column1" = Table.ExpandRecordColumn(ChangedType, "Column1", {"created_at", "from_user", "from_user_name", "geo", "iso_language_code", "text"}, {"CreatedDate", "FromUser", "FromUsername", "Geo", "ISOLanguageCode", "Text"}),
ChangedType1 = Table.TransformColumns(#"Expand Column1",{{"CreatedDate", DateTime.From}})
in
ChangedType1
There's a lot you can do with this design pattern, so be sure to check out the Twitter documentation for using the Search API. I uploaded this sample spreadsheet here which I created in Excel 2013 if you want to try out some different query strings for yourself. Important: Using a parameter from an Excel workbook is possible because I enabled Fast Combine before doing anything with Data Explorer. If you forget, you'll get an error that says 'Query' is accessing data sources which can not be used together. If this is you, then enabling Fast Combine should do the trick.It's also worth mentioning that version 1 of Twitter's Search API is nice because it doesn't require any special authentication, but this method is deprecated and may be eliminated at any time. The preferred method to search tweets is to use v1.1 of Twitter's Search API which uses OAuth to authenticate yourself before actually pulling any data from Twitter. If you have to do this, then you'll have the extra task of developing a utility with .NET or building a script task in SSIS to pull JSON file(s) one-by-one into a directory. Data Explorer is able to handle multiple files in a single directory, and JSON files should fall right in line with this. If any of you would like to tackle that challenge next, please leave a comment with a link to your blog or published article for all of us to read.
More Data Explorer fun coming soon. Thanks for reading!
