Suppose we want to apply a function to multiple columns in Tableau Prep Builder – in this particular case, we choose the ‘Round’ function. With the current version 2021.3 of Tableau Prep Builder, each function can be applied to one column only. However, to apply a function to multiple columns, we can use the suggested workarounds:
Transformation without Row-ID nr
We can use Pivots to put all values we want to transform into one column.
Pivots
Pivoting your data in tableau, simply means to turn the rows of your table into columns and vice versa. Tableau refers to this transformation as “tall” (transforming to rows from columns) and “wide” (transforming to columns from rows). You can find more information about pivots in Tableau here: https://help.tableau.com/current/prep/en-us/prep_pivot.htm
Back to rounding values in multiple columns. Let’s take the ‘Superstore’ datasource (we’ve deleted the part after ‘clean2’ and customized some columns – notice, we start with approx. 16k rows).
From here, we apply the Round function to the columns: ‘Discount_2020’, ‘Discount_2021’, ‘Sales’, ‘Profit’.
We rename the new calculated field the same as the column holding all the pivoted values, to prevent creating an extra new column which we won’t use in this case.
After the rounding transformation, we notice the number of rows has drastically decreased and the values are no longer rounded!
Transformation with the Row-ID nr
Because the Pivot function is quite similar to the ‘Aggregate’ function, we seem to have lost some rows and the rounding has not really worked. To solve this, we can add a new column that creates a new ID for each row before performing the entire transformation. We use the ‘row_number’ function.
After repeating the same steps for rounding as above, we get the following results:
We see now that we have retained the same nr of rows as from the beginning. The ‘Row-ID’ can now be removed and the columns can be re-arranged in the preferred order.
There you have it! You can now transform multiple columns at once using this technique. It is also possible to use other functions in Tableau Prep Builder to affect multiple columns at once.
Did you find this information useful/informative? We host regular training sessions (including free ones) on our website. Please feel free to enroll in one of them (The trainings can be in Dutch or English)