• Have you ever wondered if there is a difference between creating calculated columns via Transformations versus the Insert menu?
  • Have you ever worried about breaking things when changing data types?

This is the last week of the Transformation series, and this series will wrap up with transformations for calculating new columns, calculating and replacing columns and changing data types.  In discussing these last three, we’ll ask and answer the questions of …

  1. Why would you calculate columns or calculate and replace columns with Transformations?
  2. What happens why you change the data type of a column of data that is being used?

Before I dive into those, here are the steps to add each of these transformations.

Steps to Add Change Data Type Transformation

  1. Go to the Insert menu, select Transformations
  2. Select Calculate and replace column from the Transformations drop down  
  3. Click the Add button
  4. Highlight the columns to be changed (use SHIFT or CTRL) to select multiple.
  5. Set the new data type in the New data type drop down.  Note, you may only make one change at a time (ex. to Real or to String), even though you may make that change to multiple columns at a time.
  6. Click OK.

Steps to Add Calculate New Column Transformation

  1. Go to the Insert menu, select Transformations
  2. Select Calculate and replace column from the Transformations drop down
  3. Click the Add button
  4. Write the expression in the custom expression dialog
  5. Use the Column name text box to name the column  
  6. Click OK

Steps to Add Calculate and Replace Columns

  1. Go to the Insert menu, select Transformations
  2. Select Calculate and replace column from the Transformations drop down
  3. Click the Add button
  4. Make sure to make a selection in the Column to replace drop down in the top right of the custom expression dialog.  
  5. Write the expression in the custom expression dialog.
  6. Use the Column name text box to name the column.
  7. Click OK.

With those steps out of the way, let’s talk about those two key questions…

Question 1– Why would you calculate columns or calculate and replace columns with Transformations?

Answer — You must use transformations to add calculated columns if you want those calculated columns to be used in a join or to be available to pivot or unpivot.  Calculated columns, when added via the Insert menu, will not be available for joins, pivots or unpivots.

Question 2 — What happens why you change the data type of a column of data that is being used?

Answer — I would swear that in the past when I have changed the data type of a column that was used in a calculated column, my DXP became embedded.  However, I ran a few tests for this post, and I don’t see that happening, at least not anymore.  However, it will impact calculations.

Here is my starting table.  Gas and Oil Prod columns are Real and the calculated columns using those columns are also real.

Here I changed Gas Prod to Currency, and as you can see, all of the calculations using GasProd are now also currency.  I also recall having issues with frozen columns when creating calculations with different data types, but I wasn’t able to replicate any of these issues in my tests, so it appears they have been fixed by version 7.6 of the software.

I hope you found the transformation series helpful.  You may reach me at julie@bigmountainanalytics.com with any questions.

Written by Julie Schellberg of Big Mountain Analytics, LLC
Residing in Whitefish, MT, an analytics partner with Ruths.ai. Specializing in Spotfire analytics, dabbling in Power BI and R.