Restructuring Data

cleaning operations

- filtering, adding, renaming, splitting, grouping, or removing fields. You can perform cleaning operations in most step types in your flow. You can also perform cleaning operations in the data grid in a cleaning step

Split data fields

- separate values based on a delimiter or a repeated pattern of values present in each row of the field. -work best with fields that have a consistent structure, such as all having just the first and last name with no middle names. -the Split and Custom Split menu options are available from both the Data Source page in the drop down and the Data pane by right clicking field of a worksheet *In Tableau Cloud and Tableau Server, Split and Custom Split are not available as menu options, but the same capabilities can be accomplished by creating a calculated field with a formula that uses the SPLIT function from either the Data Source page or the Data pane of a worksheet.

Create a custom split

1. Click the drop-down arrow on the header of the field you want to split and select Custom Split. 2. In the Custom Split dialog box, type the separator you wish to use for the custom split. In this example, we'll use the separator "|". 3. Select the Split off options. In this example, we want to split off the first two columns, so we'll choose the First 2. 4.Click OK. - It's a best practice to give the newly-created fields meaningful names.

Turn on Data Interpreter

1.) From the Connect pane, connect to an Microsoft Excel spreadsheet or other connector that supports Data Interpreter such as text (.csv) files, PDF files, or Google sheets. 2.) On the Data Source page, drag a table to the canvas (if needed) 3.)Notice any data inconsistences 4.)Select the Use Data Interpreter check box to see if Data Interpreter can help identify just the necessary fields and values in your data. *Data Interpreter works with the data that Tableau will use for analysis, but does not change the underlying data source.

Add fields to a pivot

1.) Select the additional column. 2.) Click the drop-down arrow next to the column name. 3.) Select Add Data to Pivot.

To pivot data (In wide format to tall format)

1.) Select two or more columns in the data grid. (Hold the Ctrl key to select multiple columns.) 2.) From any of the selected columns, click the drop-down arrow next to the column name. 3.) Select Pivot.

Review the results of Data Interpreter

1.) click Review the results. 2.) A copy of your data source opens in Microsoft Excel, showing the Key for the Data Interpreter tab. Review the key to find out how to read the results. 3.) If Data Interpreter does not provide the expected results, clear the Cleaned with Data Interpreter 4.)if Data Interpreter has misidentified the range of the found table, click the drop-down arrow on that table, and then select Edit Found Table to revise

Custom Split use cases

1.) when the number of separators varies from value to value. 2.) When you want to split off portions of the original field on the left-hand side of one of the separators, set Split off to First and enter the number of columns. For example, the settings below for "MX-2011-158771" will generate a column for "MX" and another for "2011". 3.) to split off portions of the original field on the right-hand side of one of the separators, set Split off to Last and enter the number of columns. For example, the settings below for "MX-2011-158771" will generate a column for "2011" and another for "158771". 3.) to split off all portions of the original field, set Split off to All. 4.)to split off all portions of the original field based on a specific delimiter that Tableau may not select automatically, set the separator to the desired character.

You're working with a data source that needs formatting and restructuring. How can Data Interpreter help you get your data ready for analysis?

Automatically detect and remove formatting like titles, notes, footers, and empty cells from the data you want to analyze.

splitting data

Fields generated by a split can't be used in a pivot. splits are only available for fields that are a string data type

When DI is not available

If data format can already be interpreted by Tableau Exceeds max # number rows or columns Unsupported files (anything other than Microsoft Excel, CSV, PDF, and Google Sheets format.)

Divide a column with Custom Split

NOTE: manually choose which delimiter to use for the split, as well as which segments of the data to include. can separate a field's values into up to ten new fields. new fields generated by the custom split are always a string data type. 1.) drop-down arrow on the header of the field you want to split and select Custom Split. 2.) In the Custom Split dialog box, type the separator you wish to use 3.) Select the Split off options

Calculated fields produced by Split and Custom Split

Split and Custom Split create calculated fields. Order ID - Custom field is TRIM( SPLIT( [Order ID], "-", -1 ) ). You can edit these calculations in the calculated field editor.

You're working with a data source that has all of its data on a single Excel worksheet and its structure has multiple sub-tables. Which is the best way to get this data ready for analysis?

Use Data Interpreter to automatically identify the sub-tables.

Data Interpreter

can automatically detect and bypass the titles, notes, footers, and empty cells in a data source Use Data Interpreter to prepare data in files, including those in Microsoft Excel, CSV, PDF, and Google Sheets format.

Divide a column with Split

from data source page, click the drop-down arrow on the header of the field and select Split.