Transform Data By Example—a Microsoft Garage project for Excel

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

A common problem facing Excel users is data wrangling—transforming raw data into a different format so that it can be used for further analysis. For many common data domains—such as date-time, addresses, phone numbers and URLs—sophisticated transformation functions and services exist but are locked up in code repositories such as Stack Overflow, .NET and GitHub, or services such as Bing Maps. For Excel users attempting to perform a specific data transformation task, these functions are difficult to find and use.

Transform Data By Example—auto-magic? You decide

Transform Data By Example is an Excel add-in that makes it easy to quickly find relevant transformation functions for a specific data transformation task. All you need to do is provide a few examples of the desired output, and Transform Data By Example automatically returns a ranked list of relevant data transformation functions from the large repository of functions it has crawled and indexed. Simply click one of the returned functions to see the results of applying that function on the data.

In the example below, the user has a collection of addresses in the United States in various formats and needs to extract city, state and zip code information for aggregate analysis. The user only needs to provide a few output examples in the column next to the input and then click Get Transformations! Within a few seconds, Transform Data By Example finds relevant transformation functions from a large collection of functions it indexes, and then automatically uses these functions to compose and generate new code that can perform the exact transformation the user specified. All the heavy lifting happens under the cover and is transparent to users. There is no need to write code or leave the familiar Excel interface.

Transform Data By Example 1

Transform Data By Example 2

Extensible engine for transformations

In addition to the large collection of data transformation functions that Transform Data By Example already indexes, users can add their own data transformation code to the collection through an easy extensibility interface below. Transform Data By Example makes the new transformation functionality instantly searchable.

Transform Data By Example 3

Like a web search engine, Transform Data By Example continuously improves its quality by leveraging implicit user feedback. The more you use it, the better it gets. At Microsoft Research, we’re constantly developing and improving Transform Data By Example, so be sure to give it a try and let us know what you think at our UserVoice forum. We greatly value your direct feedback to help us get better at helping you wrangle that data!

Learn more

—The Microsoft Research team