Tableau Prep



Tableau Prep Builder Version 2019.4.2 and later and on the web: In the Profile pane, select the fields that you want to pivot, then right-click or Ctrl-click (MacOS) and select Pivot Columns to Rows from the menu. If using this option, skip to step 7. All versions: Click the plus icon, and select Add Pivot from the context menu. For self-service data preparation, Tableau Prep is relatively easy to use—as long as you know how to clean and organize your datasets. Carl Allchin, from The Information Lab in London, gets you up to speed on Tableau Prep through a series of practical lessons that include methods for preparing, cleaning, automating, organizing, and outputting your datasets.

  1. Tableau Prep Vs Tableau Desktop
  2. Tableau Prep Vs Desktop
  3. Tableau Prep Conductor
  4. Tableau Prep Builder

Note: Starting in version 2020.4.1, you can now create and edit flows in Tableau Server and Tableau Online. The content in this topic applies to all platforms, unless specifically noted. For more information about authoring flows on the web, see Tableau Prep on the Web.

Sometimes analyzing data from a spreadsheet or crosstab format can be difficult in Tableau. Tableau prefers data to be 'tall' instead of 'wide', which means that you often have to pivot your data from columns to rows so that Tableau can evaluate it properly.

However you may also have scenarios where your data tables are tall and narrow and are too normalized to properly analyze. For example a sales department that tracks advertising spend in two columns, one called Advertising that contains rows for radio, television and print and one column for total spent. In this type of scenario, to analyze this data as separate measures you would need to pivot that row data to columns.

Tableau Prep

But what about pivoting larger data sets or data that changes frequently over time? You can use a wildcard pattern match to search for fields that match the pattern and automatically pivot the data.

Use one of the following options when pivoting your data:

  • Pivot columns to rows

  • Use wildcard search to instantly pivot fields based on a pattern match (Tableau Prep Builder version 2019.1.1 and later and on the web).

  • Pivot rows to columns (Tableau Prep Builder version 2019.1.1 and later and on the web).

No matter how you pivot your fields, you can interact directly with the results and perform any additional cleaning operations to get your data looking just the way you want it. You can also use Tableau Prep's smart default naming feature to automatically rename your pivoted fields and values.

Pivot columns to rows

Use this pivot option to go from wide data to tall data. Pivot from columns to rows on one or more groups of fields. Select the fields that you want to work with and pivot the data from columns to rows.

  1. Connect to your data source.

  2. Drag the table that you want to pivot to the Flow pane.

  3. Do one of the following:

    • Tableau Prep Builder Version 2019.4.2 and later and on the web: In the Profile pane, select the fields that you want to pivot, then right-click or Ctrl-click (MacOS) and select Pivot Columns to Rows from the menu. If using this option, skip to step 7.
    • All versions: Click the plus icon, and select Add Pivot from the context menu.

      Select Fields (Tableau Prep Builder version 2019.4.2 and later and on the web)Flow Step Menu (all versions)
  4. (Optional) In the Fields pane, enter a value in the Search field to search the field list for fields to pivot.

  5. (Optional) Select the Automatically rename pivoted fields and values check box to enable Tableau Prep to rename the new pivoted fields using common values in the data. If no common values are found, the default name is used.
  6. Select one or more fields from the left pane, and drag them to the Pivot1 Values column in the Pivoted Fields pane.

  7. (Optional) In the Pivoted Fields pane, click the plus icon to add more columns to pivot on, then repeat the previous step to select more fields to pivot. Your results appear immediately in both the Pivot Results pane and the data grid.

    Note: You must select the same number of fields that you selected in Step 5. For example if you selected 3 fields to initially pivot on, then each subsequent column that you pivot on must also contain 3 fields.

  8. If you didn't enable the default naming option or if Tableau Prep couldn't automatically detect a name, edit the names of the fields. You can also edit the names of the original fields in this pane to best describe the data.

  9. (Optional) Rename the new Pivot step to keep track of your changes. For example 'Pivot months'.

  10. To refresh your pivot data when data changes, run your flow. If new fields are added to your data source that need to be added to the pivot, manually add them to the pivot.

Example: Pivoting on multiple fields

This example shows a spreadsheet for pharmaceutical sales, taxes and totals by month and year.

By pivoting the data you can create rows for each month and year and individual columns for sales, taxes and totals so that Tableau can more easily interpret this data for analysis.

Watch 'pivot on multiple field' in action.

Use wildcard search to pivot

If you work with large data sets or if your data frequently changes over time, starting in Tableau Prep Builder version 2019.1.1 and on the web, you can use a wildcard search when pivoting columns to rows to instantly pivot your data based on a wildcard pattern match.

Tableau Prep Vs Tableau Desktop

If new fields are added or removed that match the pattern, Tableau Prep detects the schema change when the flow is run and the pivot results are automatically updated.

  1. Connect to your data source.

  2. Drag the table that you want to pivot to the Flow pane.

  3. Click the plus icon, and select Add Pivot from the context menu.

  4. In the Pivoted Fields pane, click on the link Use wildcard search to pivot .

  5. Enter a value or partial value that you want to search for. For example, enter Sales_ to match fields that are labeled as sales_2017, sales_2018 and sales_2019.

    Do not use asterisks to match the pattern unless they are part of the field value that you are searching for. Instead click the Search Options button to select how you want to match the value. Then press Enter to apply the search and pivot the matching values.

  6. (Optional) In the Pivoted Fields pane, click the plus icon to add more columns to pivot on, then repeat the previous step to select more fields to pivot.

  7. If you didn't enable the default naming option or if Tableau Prep couldn't automatically detect a name, edit the names of the fields.

  8. To refresh your pivot data when data changes, run your flow. Any new fields added to your data source that match the wildcard pattern are automatically detected and added to the pivot.

  9. If the results aren't what you expect, try one of the following options:

    • Enter a different value pattern in the Search field and press enter. The pivot will automatically refresh and show the new results.

    • Manually drag additional fields to the Pivot1 Values column in the Pivoted Fields pane. You can also remove fields that were added manually by dragging them off the Pivot1 Values column and dropping them in the Fields pane.

      Note: Fields that were added from the wildcard search results can't be removed by dragging them off the Pivot1 Values column. Instead try using a more specific pattern to match the search results you are looking for.

Pivot rows to columns

In Tableau Prep Builder version 2019.1.1 and later and on the web, pivot rows to columns if your data is too normalized and you need to create new columns - going from tall data to wider data.

For example if you have advertising costs for each month that includes all advertising types in one column, if you pivot the data from rows to columns you can then have a separate column for each advertising type instead, making the data easier to analyze.

You can select one field to pivot on. The field values for that field are then used to create the new columns. Then, select a field to use to populate the new columns. These field values are aggregated and you can select the type of aggregation to apply.

Because aggregation is applied, pivoting columns back to rows won't reverse this pivot action. To reverse a row to column pivot type, you will need to undo the action. Either click the Undo button on the top menu, remove the fields from the Pivoted Fields pane or delete the pivot step.

  1. Connect to your data source.

  2. Drag the table that you want to pivot to the Flow pane.

  3. Click the plus icon, and select Add Pivot from the context menu.

  4. In the Pivoted Fields pane, select Rows to Columns from the drop-down list.

  5. (Optional) In the Fields pane, enter a value in the Search field to search the field list for fields to pivot
  6. Select a field from the left pane, and drag it to the Field that will pivot rows to columns section in the Pivoted Fields pane.

    Note: If the field you want to pivot on has a data type of date or datetime, you will need to change the data type to string to pivot it.

    The values in this field will be used to create and name the new columns. You can change the column names in the Pivot Results pane later.

  7. Ce-infosys others driver download for windows. Select a field from the left pane and drag it to the Field to aggregate for new columns section in the Pivoted Fields pane. The values in this field are used to populate the new columns created from the previous step.

    A default aggregation type is assigned to the field. Click the aggregation type to change it.

  8. In the Pivot Results pane, review the results and apply any cleaning operations to the new columns that you created.

  9. If the field being pivoted has a change in its row data, right-click or Ctrl-click (MacOS) on the Pivot step in the flow pane and select Refresh.

Thanks for your feedback!

Tableau Prep now supports web authoring for flows. Starting in version 2020.4.1, you can create flows to clean and prepare your data using either Tableau Prep Builder, Tableau Server, or Tableau Online. You can also manually run flows on the web and the Data Management Add-on is not required.

While most of the same Tableau Prep Builder functionality is also supported on the web, there are a few differences when creating and working with your flows.

Important: To create and edit flows on the web you must have a Creator license. However, the Data Management add-on is only required if you want to run your flows on a schedule using Tableau Prep Conductor.

Installation and Deployment

To enable users to create and edit flows on the web, configure the following settings on your server:

  • Web Authoring: Controls whether users can create and edit flows on Tableau Server or Tableau Online. For more information, see Create and Interact with Flows on the Web(Link opens in a new window) Check point network & wireless cards driver download. in the Tableau Server help.

  • Run Now: Controls whether users or only administrators can run flows manually using the Run Now option.

  • Tableau Prep Conductor: If the Data Management Add-on is enabled, enable this option to let users schedule and track flows in Tableau Server and Tableau Online.

  • Autosave: Enabled by default, this feature automatically saves a user's flow work every few seconds.

    While not recommended, administrators can disable autosave on a site using the Tableau Server REST API method 'Update Site' and flowAutoSaveEnabled setting. For more information, see Tableau Server REST API Site Methods: Update Site(Link opens in a new window). For more information about autosave on the web, see Autosave and working with drafts.

    For more information about configuring site settings, see Site Settings Reference(Link opens in a new window) in the Tableau Server help.

On Tableau Server, administrators can fine-tune the configuration of the Tableau Prep Flow Authoring processes. For more information, see Tableau Prep Flow Authoring.

Sample data and processing limits

To maintain performance while working with flows on the web, limits are applied to the amount of data you can include in a flow.

The following limits apply:

  • When connecting to files, the maximum file size is 1GB.
  • The data sampling option to include all data is not available. The default sample data limit is 1 million rows.
  • The maximum number of rows that a user can select when using large data sets is configured by the administrator. As a user, you can select the number of rows up to that limit. For more information, see tsm configuration set Options(Link opens in a new window) in the Tableau Server help.
  • In Tableau Online, the number of flow runs you can perform in a day is also limited by the site administrator. For more information, see Tableau Online Site Capacity(Link opens in a new window) in the Tableau Online help.

Available features on the web

When you create and edit flows on the web you may notice a few differences in navigation and the availability of certain features. While most features are available across all platforms, some features are limited or not yet supported in Tableau Server or Tableau Online. The following table identifies features where differences might apply.

FeatureTableau Prep BuilderTableau ServerTableau Online
Connect to Data*
Build and Organize your Flow
Set your data sample size**
Union files and database tables in the Input step***
Clean and Shape Data
Aggregate, Join, or Union Data
Use R and Python scripts in your flow ****
Create reusable flow steps (version 2019.3.2 and later)
Automatically save your flows on the web Not Applicable
Automatic file recovery Not ApplicableNot Applicable
View flow output in Tableau Desktop
Create an extract to a file
Create a published data source
Save flow output data to external databases

* Some connectors may not be supported on the web. Open the Connect pane on your server to see supported connectors.

** In Tableau Server and Tableau Online, the data sample size is subject to limits set by your administrator.

*** Input unions can't be edited or created in Tableau Server or Tableau Online. Only in Tableau Prep Builder.

**** Script steps can't be added when creating or editing a flow in Tableau Online. This is currently supported only in Tableau Prep builder and Tableau Server.

Autosave and working with drafts

Tableau Prep Vs Desktop

When you create or edit flows on the server, your work is automatically saved as a draft every few seconds so that in the event of a crash, or when closing a tab by accident, you don't lose your work.

Drafts are saved to the server and project you are signed into. You can't save or publish a draft to another server, but you can save the flow to another project on that server using the File > Publish As menu option.

Draft content can only be seen by you until you publish it. If you publish changes and need to revert them, you can use the Revision History dialog to view and revert to a previously published version. For more information about saving flows on the web, see Automatically save your flows on the web .

Publishing flows and embedding credentials

Whether you are publishing flows from Tableau Prep Builder to Tableau Server or Tableau Online or publishing a new or edited flow on the web, you have the option to embed credentials for database connections. Embedding your credentials enables flows that include database connections to run seamlessly on the server without requiring manual intervention.

Note: Embedding credentials only applies to running published flows on your server. You will still manually need to enter your credentials when editing a flow connected to a database in either Tableau Prep Builder or on the web.

When creating or editing flows on the web, you must first publish a flow before you can run it to generate your flow output for your data analysis. During publish, you can choose to embed your credentials for your database connections. By default credentials are not embedded.

Tableau Prep Conductor

You can also change your credential option from the top menu under File > Connection Credentials. For more information, see Publish a flow in web authoring.

Tableau Prep Builder

Thanks for your feedback!