Functions for Wrangling Data

Use the following functions in the Select Function drop-down list on the Data Wrangling Dialog to refine your data quality before import.

Delete Options

Delete Column: Delete the selected column.

Delete Row: Delete rows that meet the criteria you enter.

Remove Duplicate Cells: Remove duplicate cells.

Remove Duplicate Rows: Remove duplicate rows.

Delete Row included in selectors: Delete the row that is included in the selectors. This option is only available if you are using selectors to filter the data.

Delete Row not included in selectors: Delete the row that is not included in the selectors. This option is only available if you are using selectors to filter the data.

Extract Options

Extract by Fixed Length: Enter a starting and ending index value to extract the corresponding text into a new column. Index values start at one.

Extract Before Separator: Extract all text before the first instance of a separator into a new column. You can select from a list of predefined separators, enter your own, or use Regular Expression language (Regex).

Extract After Separator: Extract all text after the last instance of a separator into a new column. You can select from a list of predefined separators, enter your own, or use Regular Expression language (Regex).

Selector Options

Selectors allows you to filter your data. Rows are not deleted when they are filtered out. They are only hidden from view.

Text Selector: Display a text selector for filtering the selected column by including or excluding specific text.

Numeric Selector: Display a numeric selector for filtering the selected column by a range of numeric values.

Timeline Selector: Display a timeline selector for filtering the selected column by a date range.

Text Search: Display a text search selector for filtering the column using the text you enter.

Find and Replace Options

Find and Replace Characters in a Cell: Search for a character in a cell and replace it with the character you enter.

Find and Replace Cell: Search for a content of a cell and replace it with the text or value you enter

Remove Options

Remove Characters: Remove the characters you enter.

Remove Characters Before: Remove characters that appear before the text you enter.

Remove Characters After: Remove characters that appear after the text you enter.

You can remove dashes from the Phone Number column, so that the column contains only numbers. If you do not want the Revenue column to display cents (such as $12.43), remove the characters after to period, then remove the period. This results in a value of $12.

Split Options

Split by Fixed Length: Split the column by a fixed length. Enter the index value that corresponds with where you want to split the column. You can split the column in multiple locations by separating the values with commas. Index values start at zero.

Split On Separator: Split the column at each separator. You can select from a list of predefined separators, enter your own, or use Regular Expression language (Regex). Each split produces a new column and deletes the corresponding separator.

Split Before Separator: Split the column before each separator. You can select from a list of predefined separators, enter your own, or use Regular Expression language (Regex). Each split produces a new column and retains the separator.

Split After Separator: Split the column after each separator. You can select from a list of predefined separators, enter your own, or use Regular Expression language (Regex). Each split produces a new column and retains the separator.

Split Cell into Rows on Separator: Split a cell into a new row at each separator. You can select from a list of predefined separators or enter your own. Each split produces a new row and deletes the separator.

Transform Cell Options

To Title Case: Change the text to title case, where each word begins with an uppercase letter.

To Uppercase: Change the text to uppercase.

To Lowercase: Change the text to lowercase.

Unescape HTML: Replace the codes in HTML-encoded data with the correct characters, so that the text becomes more readable.

Change Data Type: Change the data type to number, text, or date format. See How to Define Date and Time Data Types When Wrangling Data for more information on entering date data types.

Fill Down: Replace blank cells with the value in the preceding cell. If a cell contains spaces, you must remove the spaces to make it blank.

Blank Down: Blank out cells that contain repeated data.

Pad Before: Add the characters you specify to the beginning of a cell.

Pad After: Add the characters you specify to the end of a cell.

If your Region columns contains South and North, you can add "ern" using this function. The column then contains Southern and Northern.

Whitespace Options

Trim Leading and Trailing Whitespaces: Trim leading and trailing whitespaces from a cell.

Collapse Consecutive Whitespace: Remove blocks of two or more consecutive whitespaces from a cell.

Other Options

Cluster and Edit: Group similar data into a cluster and replaces the values in the cluster.

Duplicate Column: Create a new column containing the data copied from the selected column.

Rename: Rename the selected column with the name you enter.

Concatenate Columns: Create a new column that combines the selected column with the column you select from the drop-down list.

Transpose Columns to Rows: Reshape your data by transposing information between rows and columns to visualize the information in a different way. Select the first column to be affected by the transformation and the last column to be affected. Enter a name for the Key and Value columns.