Map is a premium feature of the Data Wrangles add-in. Contact Us to get access to this feature.
Map Wrangles can be thought of as the standardize for column names and tables. That is, they take selected columns from one table and create a new table with standardized column names. Map Wrangles are all DIY or bespoke due to their specificity. See below to learn how to use and build Map Wrangles.
Map Wrangles are used to map columns from one table to another. To use a Map Wrangle, just click the run button then assign your columns.
If you would like a column to be filled in a uniform value, simply highlight a cell containing the value you wish to use then right click the appropriate check box for your column.
Data that does not match the corresponding data type will be considered "invalid." Invalid data is denoted with a green triangle in the upper left-hand corner of the cell. You can also circle invalid data in red by going to Data, Data Validation drop down menu, then clicking Circle Invalid Data.
In this example, "Hand Tools" is shown to be invalid because it is not included in our list of options (see above training data for list of options). If you wish to change the value in the cell, you can click on the drop-down menu and select and option from the list.
When running a Map Wrangle, you will notice a vertical ellipsis where you can access tools. If you click on this ellipsis, you'll see the three map tools: Magic Button, Collapse, and Copy Down. Keep reading to learn about how each one works.
The magic wand icon under the tools button represents the Magic Button. The Magic Button attempts to automatically map columns for you. If the column names are an exact match, the Magic Button can easily map the columns. Columns which have no exact match will have a pop-up menu of suggested matches. To select one of the suggested matches, simply hover over the check box to access the menu then click on the column name of your choosing.
The real magic occurs after the Magic Button has been trained by mapping the columns manually. With each use of the Map Wrangle, the Magic Button is trained. Once it is confident, it will automatically map columns based on how they have been mapped in the past. The magic button will not map columns which it has not been properly trained on.
Learned data can be deleted by editing the map wrangle, clicking on settings then clicking the "Delete Learned Data" button shown below.
The Collapse tool (highlighted in red below) allows users to collapse multi-column headers into one. This is especially useful in cases where there is a table that has headers that are both single and multi-column. Text from each row is separated by a period.
The gif above shows the Collapse tool in action. Now, the data is ready to be mapped.
The Copy Down tool (shown below in red) copies useful data down into all selected rows below. This is useful when adding a description or some other useful data to rows in a table.
The gif above shows an example use case of how to use the Copy Down tool. Now the descriptions are included in our table, which is ready to map.
Map columns can be filtered within the Wrangles Task Pane by clicking the filter icon at the top of the check box column. There are two toggle buttons to filter your columns, "Hide Matched" and "Required Only". Hide Matched will hide the columns that have already been mapped, and Required Only will show only the required columns.
Mapped columns can also be cleared by clicking the Clear Mapping button, highlighted below in red.
Recipes can be attached to the end of a map wrangle and will run once the data is mapped. In order to attach a recipe to a map wrangle, simply go to the settings tab and enter in the recipe's model id into "Post Map Recipe" box.
Map Wrangles take in two columns of training data: Name and Data Type (with an optional column for notes).
The Name column of the training data corresponds to the standardized column names which data will be mapped to. Each column does not have to be filled in with data for the Wrangle to work so feel free to add as many as needed but note that empty columns will appear in the output table for any unassigned column names.
The Data Type column of the training data dictates what the data in the column is meant to be. The Wrangle will still work if the data is of a different type, but the data will be shown as "invalid."
There are four data types to choose from: Date, Number, List and Text. Each data type can take in an argument (wrapped in square brackets) which dictates things like max/min values, max/min length, options, and date format. If a cell contains data that does not match the data type, a data validation error will be triggered in the output table. See Invalid Data below for more details.
In the example training data shown above, Description, ID, Price (USD) and Facility can be text of any length, Category is a list of options (seperated by a comma), Quantity is a number of any range, and Date is a date which must match the format mm/dd/yyyy. Note that arguments are optional for all data types but list. See below for details on each data type.
Data Type | Use Case |
---|---|
Date | The date data type can be formatted in any way you choose, ie "mm/dd/yyyy" etc. Arguments for the date data type are optional with the default format being yyyy-mm-dd. |
Number | The number data type can take in an argument of max/min values. It mirrors Python's indexing methods and looks like: number[1.15:3.14]. Here, 1.15 is the minimum value and 3.14 the maximum. With no argument given, number will not have a range. |
List | The list data type consists of a list of possible values, or "options," for the data and each option is seperated by a comma. For example list[value1, value2, value3] would only match those three values. If a different value was entered, say value4, it would be shown as invalid. If list is not given any options, then all data in that column will be invalid. List is the only data type that has no default and requires an argument. Note that because each option is seperated by a comma, an option cannot contain a comma within itself otherwise the Wrangle will consider these to be different options. For example, "Hose, Couplings, Tubing & Ducting" will be read as "Hose," "Couplings," "Tubing & Ducting." See below for a workaround. |
Text | The text data type is any text (including numbers) and takes in a length argument in the same way number takes in a range. For example text[3:12] allows for any text a minimum of 3 characters and a maximum of 12 characters long. Without a range given, text will default to a range of any length. |
Note: Lists are limited to 255 characters total when mapping in the traditional sense. See below to learn how work around this issue.
When using lists in a map, list values are separated by a comma and lists are limited to 255 character in total, but these two issues both share the same fix.
In order to work around this, you just simply add your list to a sheet and reference its location in the map wrangle. For cleanliness, I like to create a new "Reference" sheet to store these lists. This helps to preserve the data within the lists.
It is very important that the list data be preserved in the location it is referenced. If it is not preserved, the list options will not be as expected.