Wednesday, May 27, 2020

Tableau Prep Builder - A Few Tips

Tableau Prep Builder provides a GUI for users to build a data transformation flow. The GUI is intuitive and easy-to-use. Moreover, an informative help with more than 500 pages comes with this product. All these ensure that you can smoothly unleash Prep Builder's power.
Here I give a demonstration for a few use cases.

1. Insert a Step


In case you want to insert a clean step between step monthly2019 and step Union 1, hover over the flow line until you see the (+) icon popping up.
Click (+) icon and select Clean Step on the context menu.


As a result, a new clean step is inserted.


Be noted that if you click the (+) button immediately next to step monthly2019, you will end up forking a separate sub-flow as shown by the below picture.


2. Wildcard Union


Wildcard Union is an efficient data consolidation function when you deal with a number of sheets holding similar data in multiple files. This function is available in Input step.
On Multiple Files tab, you can specify search conditions for files and sheets. Prep Builder will look up them on the specified folder and consolidate all the data into one table at one time. You don't have to create a Union step to do this.

Note: Wildcard Union also applies to tables in a database. It can combine data from multiple tables which meet the conditions that you specified. In this example, I use an Excel file for the demonstration.

Specify folder and search conditions in the input step.


Click Apply button to consolidate data. As you might notice, a new field Table Names is added into the table.


On the Data Grid pane, you can review the combined data.


3. Merge


In the light of Union/Join operations, columns with duplicated data are often produced, like Branch and Branch 1 in the below example.
You can merge them into one by using Merge function in Tableau Prep Builder. First, select the 2 columns on the List View pane and right click to show the context menu. Then click Merge menu item to merge them.
The first selected column Branch 1 is the target column. On the contrary, the latter selected Branch is the source column which is merged into the target one.


Prep Builder keeps all of the data from the target column and replaces its nulls with values from the source column. After merge, the source column is deleted.



4. Table Name Change


When you change the source table name, Prep Builder shows an alert with an exclamation mark on the input step. In the example, monthly2020 was renamed to monthly2020Updated intentionally.


To correct it, you can remove the problematic step from the flow. And drag the new table to the flow canvas. Hover over the unionSales step until you see Add, Union and Join icons showing up. Then drop the new table on Add icon.


Below is the flow with the new table monthly2020Updated added in.




5. LOD Expression


Simply click Create Calculated Fields button and type a LOD calculation on the pop-up dialog. Prep Builder only supports FIXED expression. It follows the same syntax and semantic as that in Tableau Desktop.
Additionally, Create Calculated Fields function seems not to be available in Input step, Aggregate step and Output step.


The calculation result can be viewed on the Data Grid pane.


No comments:

Post a Comment

React - Makeover in React: W3Schools How To

When it comes to building a React app, I get used to searching for a UI library to build the front pages, a straightforward way to achieve f...