Tableau Prep Builder: Starting Introductory Challenge, 2023 Week 1

To master the data preparation process in Tableau Prep Builder we can explore different functions and practice regularly. One of the best ways to excel in this tool is to complete data prep challenges. Tableau Prep Challenge offers a series of exercises for beginners to advanced users every week, challenging users to solve the data preparation problem.

Today, we will work on one of the introduction challenges to start off and get our hands dirty to clean up the data. You can access this challenge in the link here.

2023 Week 1 Introductory Month Challenge: The Data Source Bank

Instruction for the Challenge

  • Input the data
  • Split the Transaction Code to extract the letters at the start of the transaction code. These identify the bank that processes the transaction.
    • Rename the new field with the Bank code 'Bank'.
  • Rename the values in the Online or In-person field, Online for the 1 value and In-Person for the 2 values.
  • Change the date to be the day of the week.
  • Different levels of detail are required in the outputs. You will need to sum up the values of the transactions in three ways:
    • Total Values of Transactions by each bank
    • Total Values by Bank, Day of the Week, and Type of Transaction (Online or In-Person)
    • Total Values by Bank and Customer Code
  • Output each data file.

Let’s Start the Challenge

Step 1. Select 'Connection'. The input file is csv so we need to select 'Text file' and input the data from the location.

Step 2: Add a 'Clean Step'. In the 'Transaction Code' field select the ellipses (...) button. Then, select the 'Split Values' and 'Automatic Split'.

Since the 'Transaction Code' has characters separated with a delimiter '-', Tableau Prep automatically detects where to split. 'Transaction Code' has been split into four fields as shown in the screenshot below.

Rename the 'Transaction Code-Split 1' into 'Bank' by double clicking at the name field and replacing it as shown in the screenshot below.

Select the remaining three fields, right click and remove to delete.

Step 3: To change the 'Online or In-person' field from numerical data type to string select the '#' button on the field and select 'String'

In the 'Online or In-person' field, double click on value 1 and replace it with 'Online'. Similarly, double-click on value 2 and replace it with 'In-person'

Step 4: In the 'Transaction Date' field, click on the ellipses (...) button, select 'Convert Dates', and select 'Day of the Week'. Double click the Transaction Date and rename it to 'Day of Week'

Step 5: In this step, we are summing up the total values of transactions by each bank. Add an 'Aggregation Step' in the 'Clean 1' step and rename it as 'Total by Bank'. In the 'Setting' tab select 'Bank' and drag it into 'Grouped Fields'. Similarly, select 'Value' and drag it into 'Aggregated Fields' as shown below.

Step 6: In this step, we are summing up the total values of transactions by Bank, 'Day of Week', and 'Online or In-person'. Add an 'Aggregation Step' in the 'Clean 1' step and rename it as 'Total by Bank Day of the week and Transaction Type'. In the 'Setting' tab select 'Bank', 'Day of Week',  and 'Online or In-Person' fields and drag it into 'Grouped Fields'. Similarly, select 'Value' and drag it into 'Aggregated Fields' as shown below.

Step 7: In this step, we are summing up the total values of transactions by 'Bank', and 'Customer Code'. Add an 'Aggregation Step' in the 'Clean 1' step and rename it as 'Total by Bank and Customer Code'. Under the 'Setting' tab select 'Bank', and 'Customer Code' fields and drag them into 'Grouped Fields'. Similarly, select 'Value' and drag it into 'Aggregated Fields' as shown below.

Step 8: In this step, we will output files for three aggregations. In each aggregation add an output flow.

Before running the flow we need to adjust the output settings. For each file I will set the similar settings shown in the following screenshot.

Finally, click on the 'Run Flow' button to output the three files on your selected location.

Author:
Nitesh Shrestha
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab