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.
¶ Using 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.
¶ Tools
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.
¶ Magic Button
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.
¶ Flatten
The Flatten 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.
¶ Fill
The Fill 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 Fill Down tool. Now the descriptions are included in our table, which is ready to map.
Additionally, the Fill tool can be used to fill up, left, and right.
¶ Gather
The Gather tool can be used to merge selected cells into one column. This is not to be confused with the concatenate wrangle as the Gather tool works at the cell lever and expects one cell per row maximum.
The Gather tool can also be used to gather data down, instead of to the right, by selecting your data, clicking the dropdown next to the Gather Tool, then selecting Gather Row.
¶ Unmerge
The Unmerge tool allows users to unmerge cells that have been merged with the Excel Merge Cells tool. While the Excel unmerge only puts the data in the top leftmost cell, the Map Unmerge fills all cells with the same data.
¶ Filtering Map Columns
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.
¶ Using Map With a Recipe
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, click to enable a post map recipe then enter in the recipe's model id in the text box.
¶ Adding Columns
User can add columns when running a map by turning on Allow Dynamic Columns (the default setting).
With this turned on, columns can be added using the button shown below.
¶ Building a Map Wrangle
Map Wrangles take in two columns of training data: Name and Data Type (with an optional column for notes).
¶ Name
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.