Let's take a dataset with negative and positive exponential notation in a column. These exponential values must be converted to their decimal equivalent. To achieve this please try using the Split transforms to split the columns with exponential values and apply the Formula transforms on the split columns.

Steps to be followed to convert your exponential values to decimal equivalent

1. Import your dataset into DataPrep from the required data source.

The dataset contains exponential values in the Exponent column.

2. Now, use the Split transform on the Exponent column with the exponential values.

To split the column:

• Choose delimiter: Select and highlight the letter E, using which you can split the column. The selection will be highlighted in yellow. 
  
  Note: This method of selecting and applying transforms is called Text selection transforms. Click here to learn more.
  
  
• DataPrep shows a list of suggestions on the right hand side pane. 
  
  
• Hover the mouse over the Split transform suggestions for a quick preview.
  
  
  
  
  
• Click on the suggestion for a full preview, which will be displayed on the data grid.
  
  
• When you click the suggestion, the buttons Edit and Apply will appear within the suggestion.
  
  
• Click on Apply to apply the Split transform.
  
  
  

3. After applying the split transform, let's apply Formula transform on the split columns to get the decimal equivalent of the exponential value.

Formula transform to extract the decimal value from the E-notation number:

• Click on the Transform menu and select the Formula column option.
  
  
• Provide a name for the new column name in the New column name field.
  
  
• You can insert the following functions in the Formula field by typing the function names.
  
  
  Formula : mul(`Split_Column1`,pow(10,`Split_Column2`))
  
  
• Click Apply. The output will be generated in a new column.
  
  
  
  

4. Now, I will remove some of the columns created ("Exponent_split_1," Exponent_split_2") during this process to get the result. 

5. Now we have a clean dataset with the decimal equivalent of the exponential values.
   
   

Case2: When your dataset contains a column mixed with exponential values

Let's consider a dataset with a column that includes numbers, decimals, and values in exponential notation. When imported into DataPrep, the column is identified as a numeric datatype, but the exponential values are marked as invalid. To rectify this, we need to convert the exponential values into their decimal equivalents to make them valid. To achieve this, you can use the Split transforms to split the column with exponential values, Fill the empty cells with "0" and then apply the Formula transforms to the split columns.

Steps to be followed to convert your exponential values to decimal equivalent

1. Import your dataset into DataPrep from the required data source.

The dataset contains numbers, decimals, and values in exponential notation in the Value column.

2. Now, use the Split transform on the Value column with the numbers, decimals, and exponential values.

To split the column:

Steps to split the columns with exponential values can be found here.

2. DataPrep allows you to fill the empty cells in a column using the Fill empty cells transform. Fill the empty cells with value 0 using the following steps.

To fill empty cells

• Right-click the column name and select the Fill empty cells option from the context menu.
  
  
• Choose the Custom value option from the Fill empty cells using section and enter 0.
  
  
• DataPrep shows a live preview of the column during the transformation. 
  
  
• After selecting the required option, click Apply.
  
  

3. After applying the split transform, let's apply Formula transform on the split columns to get the decimal equivalent of the exponential value.

Formula transform to extract the decimal value from the E-notation number:

5. Now we have a clean dataset with the decimal equivalent of the exponential values.