{"id":321428,"date":"2017-05-17T04:30:21","date_gmt":"2017-05-17T11:30:21","guid":{"rendered":"https:\/\/www.microsoft.com\/en-us\/research\/?post_type=msr-project&#038;p=321428"},"modified":"2019-09-23T14:39:18","modified_gmt":"2019-09-23T21:39:18","slug":"transform-data-by-example","status":"publish","type":"msr-project","link":"https:\/\/www.microsoft.com\/en-us\/research\/project\/transform-data-by-example\/","title":{"rendered":"Transform Data by Example"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>Do you spend hours trying to clean, standardize, and transform data such as date-time, addresses, phone numbers, and URLs? <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/appsource.microsoft.com\/en-us\/product\/office\/WA104380727\">Transform-Data-by-Example<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> is an Excel add-in that helps you find the desired transformation function easily. All you need to do is to provide a few examples of the desired output, and Transform-Data-by-Example will automatically find relevant data transformation functions from a large collection that it has already indexed. Moreover, you can add your own data transformation code into the collection, and Transform-Data-by-Example will make them instantly searchable for you, all seamlessly within Excel. The add-in works on Excel 2016, Excel\u00a0Online, Excel 2016 for Mac, and Excel 2016 for iPad.<\/p>\n<p>As of 2017, a variant of this technology has become officially available in <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/powerbi.microsoft.com\/en-us\/blog\/power-bi-desktop-june-feature-summary\/#addColumn\">Power Query<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> (add-column-from-example), which is also available naively in Excel (from the &#8220;Data&#8221; tab -> &#8220;From Tables&#8221; -> Add Column from Examples).<\/p>\n<p><iframe loading=\"lazy\" title=\"Transform Data by Example, a Microsoft Garage project\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube-nocookie.com\/embed\/cl7RXSHdsyo?feature=oembed&rel=0\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<h2 style=\"text-align: center\">Get Your Add-in now<\/h2>\n<h2 style=\"text-align: center\">\u00a0<a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/aka.ms\/transform-data-by-example-download\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-322202 size-full\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2016\/11\/OfficeDownload_Small.png\" alt=\"officedownload_small\" width=\"206\" height=\"58\" \/><span class=\"sr-only\"> (opens in new tab)<\/span><\/a><\/h2>\n<h2><\/h2>\n<h2>\t\t\t<div class=\"moray-accordion\" data-show=\"false\">\n\t\t\t\t\t\t\t<div class=\"clearfix\">\n\t\t\t\t\t<div\n\t\t\t\t\t\tclass=\"btn-group align-items-center mb-g float-sm-right\"\n\t\t\t\t\t\trole=\"group\"\n\t\t\t\t\t\tdata-mount=\"collapse-controls\"\n\t\t\t\t\t\tdata-bi-aN=\"accordion-collapse-controls\"\n\t\t\t\t\t\tdata-target=\"#accordion-1\">\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"expand\" type=\"button\" data-bi-cN=\"Expand all\">Expand all<\/button>\n\t\t\t\t\t\t<span aria-hidden=\"true\"> | <\/span>\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"collapse\" type=\"button\" data-bi-cN=\"Collapse all\">Collapse all<\/button>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<ul class=\"accordion\" id=\"accordion-1\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<li class=\"m-0\">\n\t\t\t<h4 class=\"accordion-header\">\n\t\t\t\t<button\n\t\t\t\t\tclass=\"btn btn-collapse m-0\"\n\t\t\t\t\ttype=\"button\"\n\t\t\t\t\tdata-mount=\"collapse\"\n\t\t\t\t\tdata-target=\"#collapse-2\"\n\t\t\t\t\taria-expanded=\"false\"\n\t\t\t\t\taria-controls=\"collapse-2\">\n\t\tHow To Use\t\t\t\t<\/button>\n\t\t\t<\/h4>\n\t\t\t<div class=\"collapse\" id=\"collapse-2\">\n\t\t\t\t<div class=\"accordion-body\">\n\t\t\t\t\t\t\t\t\t\t<p>Once you installed the add-in, you can launch it within Excel by clicking on the &#8220;<strong>Insert<\/strong>&#8221; tab, then clicking &#8220;<strong>My Add-ins<\/strong>&#8221;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-322256\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2016\/11\/Step-InsertAddin.png\" alt=\"step-insertaddin\" width=\"271\" height=\"153\" \/><\/p>\n<p>1. Enter a few (at least two) output examples.\u00a0Select an empty cell in the column you want to fill in and click &#8220;<strong>Get Transformations<\/strong>&#8221; button.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-362555 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/FrontPage.png\" alt=\"\" width=\"593\" height=\"524\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/FrontPage.png 975w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/FrontPage-300x265.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/FrontPage-768x679.png 768w\" sizes=\"(max-width: 593px) 100vw, 593px\" \/><\/p>\n<p>2. The engine returns one or more transformations that are consistent with your input\/output examples. You can preview the results of a suggestion by hovering on it. You can compare two suggestions by clicking pin <img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-362636\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/pin.png\" alt=\"\" width=\"24\" height=\"24\" \/>to pin one\u00a0\u00a0and hovering over the other. You can apply and unapply\u00a0the results of a suggestion by clicking on the &#8220;Apply\/Unapply&#8221; <img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-362633\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/Apply.png\" alt=\"\" width=\"28\" height=\"23\" \/>\u00a0icon.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-362558 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/TransformationResult_URL.png\" alt=\"\" width=\"605\" height=\"531\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/TransformationResult_URL.png 975w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/TransformationResult_URL-300x263.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/TransformationResult_URL-768x674.png 768w\" sizes=\"(max-width: 605px) 100vw, 605px\" \/><\/p>\n<p>3. If none of the returned suggestions\u00a0can solve your task, consider providing a few additional examples and try &#8220;<strong>Get Transformations<\/strong>&#8221; again.<\/p>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/li>\n\t\t<h2><\/h2\t\t\t\t\t\t\t<\/ul>\n\t\t<\/div>\n\t\t<\/h2>\n<h2>\t\t\t<div class=\"moray-accordion\" data-show=\"false\">\n\t\t\t\t\t\t\t<div class=\"clearfix\">\n\t\t\t\t\t<div\n\t\t\t\t\t\tclass=\"btn-group align-items-center mb-g float-sm-right\"\n\t\t\t\t\t\trole=\"group\"\n\t\t\t\t\t\tdata-mount=\"collapse-controls\"\n\t\t\t\t\t\tdata-bi-aN=\"accordion-collapse-controls\"\n\t\t\t\t\t\tdata-target=\"#accordion-3\">\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"expand\" type=\"button\" data-bi-cN=\"Expand all\">Expand all<\/button>\n\t\t\t\t\t\t<span aria-hidden=\"true\"> | <\/span>\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"collapse\" type=\"button\" data-bi-cN=\"Collapse all\">Collapse all<\/button>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<ul class=\"accordion\" id=\"accordion-3\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<li class=\"m-0\">\n\t\t\t<h4 class=\"accordion-header\">\n\t\t\t\t<button\n\t\t\t\t\tclass=\"btn btn-collapse m-0\"\n\t\t\t\t\ttype=\"button\"\n\t\t\t\t\tdata-mount=\"collapse\"\n\t\t\t\t\tdata-target=\"#collapse-4\"\n\t\t\t\t\taria-expanded=\"false\"\n\t\t\t\t\taria-controls=\"collapse-4\">\n\t\tSample Data\t\t\t\t<\/button>\n\t\t\t<\/h4>\n\t\t\t<div class=\"collapse\" id=\"collapse-4\">\n\t\t\t\t<div class=\"accordion-body\">\n\t\t\t\t\t\t\t\t\t\t<p><a href=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/Sample_Reduced-1.xlsx\">Sample<span class=\"sr-only\"> (opens in new tab)<\/span><\/a>: Name, Phone number, Address, etc.<\/p>\n<p><a href=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/Sample_Reduced-1.xlsx\">\u00a0<span class=\"sr-only\"> (opens in new tab)<\/span><\/a><\/p>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/li>\n\t\t<h2><\/h2\t\t\t\t\t\t\t<\/ul>\n\t\t<\/div>\n\t\t<\/h2>\n<h2>\t\t\t<div class=\"moray-accordion\" data-show=\"false\">\n\t\t\t\t\t\t\t<div class=\"clearfix\">\n\t\t\t\t\t<div\n\t\t\t\t\t\tclass=\"btn-group align-items-center mb-g float-sm-right\"\n\t\t\t\t\t\trole=\"group\"\n\t\t\t\t\t\tdata-mount=\"collapse-controls\"\n\t\t\t\t\t\tdata-bi-aN=\"accordion-collapse-controls\"\n\t\t\t\t\t\tdata-target=\"#accordion-5\">\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"expand\" type=\"button\" data-bi-cN=\"Expand all\">Expand all<\/button>\n\t\t\t\t\t\t<span aria-hidden=\"true\"> | <\/span>\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"collapse\" type=\"button\" data-bi-cN=\"Collapse all\">Collapse all<\/button>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<ul class=\"accordion\" id=\"accordion-5\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<li class=\"m-0\">\n\t\t\t<h4 class=\"accordion-header\">\n\t\t\t\t<button\n\t\t\t\t\tclass=\"btn btn-collapse m-0\"\n\t\t\t\t\ttype=\"button\"\n\t\t\t\t\tdata-mount=\"collapse\"\n\t\t\t\t\tdata-target=\"#collapse-6\"\n\t\t\t\t\taria-expanded=\"false\"\n\t\t\t\t\taria-controls=\"collapse-6\">\n\t\tFAQ\t\t\t\t<\/button>\n\t\t\t<\/h4>\n\t\t\t<div class=\"collapse\" id=\"collapse-6\">\n\t\t\t\t<div class=\"accordion-body\">\n\t\t\t\t\t\t\t\t\t\t<p><strong>Q1. What Excel versions are supported?<\/strong><\/p>\n<p>Currently, <em>Transform Data by Example<\/em> only works with Excel 2016 and Excel\u00a0Online. Earlier versions of Excel are not currently supported due to JavaScript compatibility issues.<\/p>\n<p><strong>Q2. What transformation functionalities are supported and indexed?<\/strong><\/p>\n<p><em>Transform Data by Example<\/em> indexes a large number of transformation functions from .NET libraries and GitHub with permissive licenses, and certain\u00a0frequently accessed\u00a0Wikipedia tables that can be used for transformation (e.g., states-to-abbreviations).\u00a0With these resources,\u00a0<em>Transform Data by Example <\/em>already\u00a0supports many transformation scenarios in head domains such as name, phone number, address, URL, etc., as well as a variety of tail domains.<\/p>\n<p>While <em>Transform Data by Example<\/em> supports transformations in many useful domains, it will not support all transformation functionalities out-of-box, because\u00a0it is possible that the crawler has not seen the libraries that can be useful for your transformation task. If you have the code\/library\/web-service for the required transformation, you can use the extensibility feature to make these additional transformation functionalities available in your local version of the add-in.<\/p>\n<p><strong>Q3. What Excel native functions are used by <em>Transform Data by Example<\/em>?<\/strong><\/p>\n<p>While <em>Transform Data by Example<\/em> is currently an Excel add-in, it does not rely exclusively on Excel functions. In fact, it indexes and searches transformation functions from sources such as .NET and GitHub, which provide functionalities well beyond native Excel functions. It should also be noted that\u00a0presently, not all\u00a0Excel functionalities are indexed by <em>Transform Data by Example.<\/em><\/p>\n<p><strong>Q4. What is the difference between <em>Transform Data by Example <\/em>and FlashFill which is also an Excel feature?<\/strong><\/p>\n<p>FlashFill is a popular feature in Excel that also uses an example-driven interface to automatically produce transformations. The difference is that FlashFill is mainly intended for simple string-based transformations, while <em>Transform Data by Example<\/em> leverages a large collection of sophisticated functions that enables it to perform semantic and domain-specific transformations beyond string manipulations. Please refer to our sample examples\u00a0for more information.<\/p>\n<p><strong>Q5. How many output examples are needed to use <em>Transform Data by Example<\/em>?<\/strong><\/p>\n<p>We recommend users to provide 3 or more (ideally different) output examples, but it is not a hard requirement. Using 1 or 2 output examples can still work fine in certain cases, but for some datasets the transformation problem may become under-specified with too few output examples. This can force our system to generate many incorrect transformations intermixed with correct ones, creating difficulty for users to find the correct results. We recommend 3 or more output examples as a general rule of thumb.<\/p>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/li>\n\t\t<h2><\/h2\t\t\t\t\t\t\t<\/ul>\n\t\t<\/div>\n\t\t<\/h2>\n<h2>\t\t\t<div class=\"moray-accordion\" data-show=\"false\">\n\t\t\t\t\t\t\t<div class=\"clearfix\">\n\t\t\t\t\t<div\n\t\t\t\t\t\tclass=\"btn-group align-items-center mb-g float-sm-right\"\n\t\t\t\t\t\trole=\"group\"\n\t\t\t\t\t\tdata-mount=\"collapse-controls\"\n\t\t\t\t\t\tdata-bi-aN=\"accordion-collapse-controls\"\n\t\t\t\t\t\tdata-target=\"#accordion-7\">\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"expand\" type=\"button\" data-bi-cN=\"Expand all\">Expand all<\/button>\n\t\t\t\t\t\t<span aria-hidden=\"true\"> | <\/span>\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"collapse\" type=\"button\" data-bi-cN=\"Collapse all\">Collapse all<\/button>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<ul class=\"accordion\" id=\"accordion-7\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<li class=\"m-0\">\n\t\t\t<h4 class=\"accordion-header\">\n\t\t\t\t<button\n\t\t\t\t\tclass=\"btn btn-collapse m-0\"\n\t\t\t\t\ttype=\"button\"\n\t\t\t\t\tdata-mount=\"collapse\"\n\t\t\t\t\tdata-target=\"#collapse-8\"\n\t\t\t\t\taria-expanded=\"false\"\n\t\t\t\t\taria-controls=\"collapse-8\">\n\t\tTroubleshooting Problems\t\t\t\t<\/button>\n\t\t\t<\/h4>\n\t\t\t<div class=\"collapse\" id=\"collapse-8\">\n\t\t\t\t<div class=\"accordion-body\">\n\t\t\t\t\t\t\t\t\t\t<p><strong>Q1. ERROR message: \u201cPlease select an empty cell inside the data range of the column that you are trying to fill.\u201d<\/strong><\/p>\n<p>This warning message is triggered in two cases:<\/p>\n<ul>\n<li>No valid data region was detected for transformations to be performed. There are two ways to select data region in <em>Transform Data by Example: <\/em>(1) The user can select any empty cell in the column for which output needs to be produced, in such a case a contiguous non-empty region <em>to the left<\/em> is automatically inferred as the data region (see the first figure below), or (2) The user explicitly selects a region where the last column is used as the output column, in which values are only partially filled (see the second figure below). No valid data region can be detected if neither condition above is met. Please explicitly select a data region and click \u201cGet Transformations\u201d again.<\/li>\n<li>All cells in the output column are already filled in. If there are no empty cells to populate in the output column (see example in the figure below) this warning message is raised. Please review the data region shown in a shaded area on your spreadsheet, and adjust as necessary.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-376010 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/p1.png\" alt=\"\" width=\"651\" height=\"395\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p1.png 1928w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p1-300x182.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p1-768x465.png 768w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p1-1024x620.png 1024w\" sizes=\"(max-width: 651px) 100vw, 651px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-376013 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/p2.png\" alt=\"\" width=\"657\" height=\"398\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p2.png 1928w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p2-300x182.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p2-768x465.png 768w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/p2-1024x620.png 1024w\" sizes=\"(max-width: 657px) 100vw, 657px\" \/><\/p>\n<p><strong>Q2. No result is returned after clicking \u201cGet Transformations\u201d.<\/strong><\/p>\n<p>First, please verify that a valid data region is selected. If you see warning messages displayed at the top of the result pane, please refer to Q1 above to ensure that a valid data region is selected.<\/p>\n<p>If the data region selected is valid, please check that example values provided in the output column are correct. Currently, the programs we generate are required to be consistent with <em>all<\/em> output examples provided by users. As such, a minor typo in the output column can prevent results from being produced. In some cases, you may consider removing examples that may be too restrictive.<\/p>\n<p>Lastly, it is possible that the crawler has not seen the libraries that can be useful for your transformation task. However, if you have the code\/library\/web-service for the required transformation, you can use the extensibility feature of <em>Transform Data by Example<\/em> to make additional transformation functionalities available to you. Note that all extensions you make to the service are visible only to you and not other users.<\/p>\n<p><strong>Q3. Too many results are returned, none of which look relevant.<\/strong><\/p>\n<p>This typically happens when the transformation task is under-specified, because not enough examples are provided. Please consider providing more (ideally different) output examples, and please refer to Q5 of FAQ above for more information.<\/p>\n<p><strong>Q4. I clicked on \u201cApply\/Unapply\u201d by mistake and now all empty cells in the output column are filled with values. How do I undo this action?<\/strong><\/p>\n<p>To undo this action, simply click on the same \u201cApply\/Unapply\u201d button again. This button works in toggle mode. Note that the Excel built-in \u201cundo\u201d functionality does not work for data populated by the Add-in.<\/p>\n<p><strong>Q5. ERROR message \u201cSorry, we had to restart because this add-in wasn\u2019t responding.\u201d<\/strong><\/p>\n<p>This happens when the connection to the backend service is lost or unstable. Please right-click any white open area in the Add-in, then click \u201creload\u201d and try again.<\/p>\n<p><strong>Q6. My output data are formatted into strange numbers that I don\u2019t recognize.<\/strong><\/p>\n<p>Occasionally, numbers are recognized as dates and other built-in data types, and will be automatically formatted by Excel. Please select the data region in question, right-click your mouse, click \u201cformat text\u201d in the menu, then click \u201cText\u201d from the list of options, and finally click \u201cOK\u201d. This will disable auto-formatting in the data region. Please refer to <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/support.office.com\/en-us\/article\/Stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8\">this article<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> for more information.<\/p>\n<p><strong>Q7. When I scroll on the Add-In, Excel appears to freeze and become unresponsive. <\/strong><\/p>\n<p>There is a known issue where Excel the presence of an Add-In causes Excel to freeze. As a workaround, if you change focus to any other application on your machine, and then change focus back to Excel, it will become responsive again.<\/p>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/li>\n\t\t<h2><\/h2\t\t\t\t\t\t\t<\/ul>\n\t\t<\/div>\n\t\t<\/h2>\n<h2><\/h2>\n<h2>\t\t\t<div class=\"moray-accordion\" data-show=\"false\">\n\t\t\t\t\t\t\t<div class=\"clearfix\">\n\t\t\t\t\t<div\n\t\t\t\t\t\tclass=\"btn-group align-items-center mb-g float-sm-right\"\n\t\t\t\t\t\trole=\"group\"\n\t\t\t\t\t\tdata-mount=\"collapse-controls\"\n\t\t\t\t\t\tdata-bi-aN=\"accordion-collapse-controls\"\n\t\t\t\t\t\tdata-target=\"#accordion-9\">\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"expand\" type=\"button\" data-bi-cN=\"Expand all\">Expand all<\/button>\n\t\t\t\t\t\t<span aria-hidden=\"true\"> | <\/span>\n\t\t\t\t\t\t<button class=\"btn btn-link m-0\" aria-disabled=\"false\" aria-pressed=\"false\" data-action=\"collapse\" type=\"button\" data-bi-cN=\"Collapse all\">Collapse all<\/button>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<ul class=\"accordion\" id=\"accordion-9\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<li class=\"m-0\">\n\t\t\t<h4 class=\"accordion-header\">\n\t\t\t\t<button\n\t\t\t\t\tclass=\"btn btn-collapse m-0\"\n\t\t\t\t\ttype=\"button\"\n\t\t\t\t\tdata-mount=\"collapse\"\n\t\t\t\t\tdata-target=\"#collapse-10\"\n\t\t\t\t\taria-expanded=\"false\"\n\t\t\t\t\taria-controls=\"collapse-10\">\n\t\tExtensibility\t\t\t\t<\/button>\n\t\t\t<\/h4>\n\t\t\t<div class=\"collapse\" id=\"collapse-10\">\n\t\t\t\t<div class=\"accordion-body\">\n\t\t\t\t\t\t\t\t\t\t<p>Even though<em> Transform Data by Example<\/em> already indexes a large collection of data transformation functions, it is also designed to be <em>extensible<\/em>, because in different industries and application domains, there are often domain-specific problems\u00a0and proprietary data sets (such as your enterprise-specific data) that <em>Transform Data by Example<\/em> has not seen and does not support yet.\u00a0 Users can add their own data transformation functions using the <em>extensibility<\/em> feature, as shown below.<\/p>\n<p>As a simple demo example, suppose you want to add a hex-to-decimal conversion functionality<em>. <\/em>You can easily\u00a0create\u00a0an <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/azure.microsoft.com\/en-us\/services\/functions\/\">Azure Function<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> and register it with <em>Transform Data by Example. <\/em><\/p>\n<p>While more tutorial of Azure Function\u00a0can be found <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-functions\/functions-create-first-azure-function\">here<span class=\"sr-only\"> (opens in new tab)<\/span><\/a>, for this example we only need to modify the hello-world created for HTTP-triggered C# function, by adding a simple function that performs hex-to-decimal conversion (highlighted in the second figure below, the code is adapted from a\u00a0StackOverflow solution <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"http:\/\/stackoverflow.com\/a\/74223\">here<span class=\"sr-only\"> (opens in new tab)<\/span><\/a>).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-366248 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/helloworld-af.png\" width=\"576\" height=\"340\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af.png 902w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af-300x177.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af-768x454.png 768w\" sizes=\"(max-width: 576px) 100vw, 576px\" \/><\/p>\n<p style=\"text-align: center\"><strong>Azure function: Hello World<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-366251 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/helloworld-af-changed.png\" width=\"581\" height=\"426\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af-changed.png 899w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af-changed-300x220.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af-changed-768x563.png 768w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/helloworld-af-changed-80x60.png 80w\" sizes=\"(max-width: 581px) 100vw, 581px\" \/><\/p>\n<p style=\"text-align: center\"><strong>Azure function: Extended with Hex-to-Decimal (changes highlighted)<\/strong><\/p>\n<p>We then\u00a0change the HTTP method from POST to GET in the dropdown menu at top-right &#8212; for simplicity we will pass parameters in URL by HTTP GET.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-366374 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/cropped-post-get-2.png\" alt=\"\" width=\"99\" height=\"170\" \/><\/p>\n<p>Finally, run this Azure Function from your browser by copying the provided function URL, and adding a &#8220;&name=ff&#8221; parameter at the end\u00a0to test\u00a0conversion for Hex value &#8220;ff&#8221;\u00a0(shown highlighted in the address box in the picture below). This will invoke the newly created Azure Function deployed in the cloud. The expected result string will show up in the browser as below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-376016 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/cropped-result-1.png\" alt=\"\" width=\"656\" height=\"141\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-result-1.png 889w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-result-1-300x64.png 300w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-result-1-768x165.png 768w\" sizes=\"(max-width: 656px) 100vw, 656px\" \/><\/p>\n<p>Now we can simply add that function URL above\u00a0to <em>Transform Data by Example<\/em>\u00a0using the extensibility interface. From the <em>Transform Data by Example<\/em>\u00a0 add-in, click on the menu button (shown below).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-366272 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/cropped-extensitibilty-1.png\" alt=\"\" width=\"392\" height=\"192\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-extensitibilty-1.png 617w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-extensitibilty-1-300x147.png 300w\" sizes=\"(max-width: 392px) 100vw, 392px\" \/><\/p>\n<p>In the next page, paste the function URL (with the parameter string) into the text box and click &#8220;Add&#8221;.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-366278 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/cropped-extensitibilty-2-1.png\" alt=\"\" width=\"215\" height=\"218\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-extensitibilty-2-1.png 373w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-extensitibilty-2-1-296x300.png 296w\" sizes=\"(max-width: 215px) 100vw, 215px\" \/><\/p>\n<p>Now if we invoke our test case by searching from Excel spreadsheet again, the result will show up as the top suggestion.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-366266 aligncenter\" src=\"https:\/\/www.microsoft.com\/en-us\/research\/uploads\/prod\/2017\/02\/cropped-filled.png\" alt=\"\" width=\"485\" height=\"221\" srcset=\"https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-filled.png 659w, https:\/\/www.microsoft.com\/en-us\/research\/wp-content\/uploads\/2017\/02\/cropped-filled-300x137.png 300w\" sizes=\"(max-width: 485px) 100vw, 485px\" \/><\/p>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/li>\n\t\t<h2><\/h2\t\t\t\t\t\t\t<\/ul>\n\t\t<\/div>\n\t\t<\/h2>\n<p>&nbsp;<\/p>\n<h2>Release Notes<\/h2>\n<p>This version of add-in works on Excel 2016, Excel\u00a0Online,\u00a0 and Excel 2016 for Mac (ver. 15.20 or later).<\/p>\n<p>&nbsp;<\/p>\n<h2>Need help? \u00a0Have feedback?<\/h2>\n<p>We will carefully review all your requests. Please visit our <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/aka.ms\/transform-data-by-example-feedback\">forum<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> and let us know. Thanks.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\"><a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/aka.ms\/transform-data-by-example-privacy\">Privacy\u00a0<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> | <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/aka.ms\/transform-data-by-example-terms\">Terms<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> | <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/aka.ms\/transform-data-by-example-feedback\">Feedback<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> | <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/aka.ms\/transform-data-by-example-download\">Rate us<span class=\"sr-only\"> (opens in new tab)<\/span><\/a> | <a class=\"msr-external-link glyph-append glyph-append-open-in-new-tab glyph-append-xsmall\" href=\"https:\/\/x.com\/TDBE_MSFT\">Follow us on Twitter<span class=\"sr-only\"> (opens in new tab)<\/span><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Do you spend hours trying to clean, standardize, and transform data such as date-time, addresses, phone numbers, and URLs? Transform-Data-by-Example is an Excel add-in that helps you find the desired transformation function easily. All you need to do is to provide a few examples of the desired output, and Transform-Data-by-Example will automatically find relevant [&hellip;]<\/p>\n","protected":false},"featured_media":321431,"template":"","meta":{"msr-url-field":"","msr-podcast-episode":"","msrModifiedDate":"","msrModifiedDateEnabled":false,"ep_exclude_from_search":false,"_classifai_error":"","footnotes":""},"research-area":[13563],"msr-locale":[268875],"msr-impact-theme":[],"msr-pillar":[],"class_list":["post-321428","msr-project","type-msr-project","status-publish","has-post-thumbnail","hentry","msr-research-area-data-platform-analytics","msr-locale-en_us","msr-archive-status-active"],"msr_project_start":"2017-05-17","related-publications":[],"related-downloads":[],"related-videos":[],"related-groups":[],"related-events":[],"related-opportunities":[],"related-posts":[],"related-articles":[],"tab-content":[],"slides":[],"related-researchers":[{"type":"user_nicename","display_name":"Vivek Narasayya","user_id":34602,"people_section":"Group 1","alias":"viveknar"},{"type":"user_nicename","display_name":"Yeye He","user_id":34992,"people_section":"Group 1","alias":"yeyehe"}],"msr_research_lab":[199565],"msr_impact_theme":[],"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-project\/321428"}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-project"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/types\/msr-project"}],"version-history":[{"count":9,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-project\/321428\/revisions"}],"predecessor-version":[{"id":610299,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-project\/321428\/revisions\/610299"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/media\/321431"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/media?parent=321428"}],"wp:term":[{"taxonomy":"msr-research-area","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/research-area?post=321428"},{"taxonomy":"msr-locale","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-locale?post=321428"},{"taxonomy":"msr-impact-theme","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-impact-theme?post=321428"},{"taxonomy":"msr-pillar","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/research\/wp-json\/wp\/v2\/msr-pillar?post=321428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}