Data Wrangler is a powerful tool that allows you to clean your data and make edits on the fly. Transforming columns has never been easier.
Using the Data Wrangler
Data Wrangler can be accessed from Data List or when adding new data.
- Wrangle Data button will become active after pressing Save when uploading new data.
- If you wish to wrangle an existing data set, go to the Data List and click the name of the data set.
- Click Wrangle Data.
- To add a new column, press Add.
- Clicking Add Column, Add Operator and Add Value boxes will allow you to create a formula to add a new column that uses the values from an existing one.
- Once your formula is set, hit OK to add the new column.
- To delete columns, select those columns by activating the checkbox.
- Hit delete.
Unpivoting transforms columns into rows.
- To Unpivot columns, select those columns by activating the checkbox.
- Hit Unpivot.
Another data set can be appended into this one using the Join function.
- Press Join.
- Search for a data set from available data.
- Press Add This Data to select the data set whose columns will be merged into your data.
- Select the columns to be joined and choose between Left Join, Right Join, Inner Join or Full Join.
White characters are extra characters that may not always be visible but is there in the data. Copying data from an HTML page can often cause White Characters to be retained. Activating this option will display these foreign characters on the data table. You can remove them from your data set by pressing Actions.
- Select Show Whitespace Chars.
- This will show all cells that have white characters in it.
- To remove white characters, select Replace Whitespace Chars under the Action menu for that column.
- Don’t enter a value to replace the characters with (i.e. leave With This box empty).
- Press OK.
This will clean your data from any code based foreign characters.
- Press Remove top rows to remove rows from the top and Remove bottom rows to remove from the bottom.
- Enter the number of rows you wish to remove.
Other Column Actions
Actions menu provides more ways to interact with columns. These features include,
Replace String – Replace text in every cell in the column range, with a string.
Replace Whitespace Chars – Replace whitespace characters within an entire column with something else. Whitespace characters are foreign characters sometimes carried over from copying data from an HTML page.
Trim – Trim an entire column up to the selected character.
Remove Rows by Condition – Set a logic and remove certain rows.
Split – Split a column into two by length of characters or selected character.
Extract – Select only from row A to row B. The data must be text.
Format – Change the data type to choose between; text, numeric, date, date time.