Data Cleansing
Last updated
Last updated
The below-mentioned transforms are available under the Data Cleansing category:
​
Clear the cell value on matching the condition specified. Operators include contains, equals, starts with, end with, and regex match. Transform applies in the same column.
The Clear Cells on Matching Value data transform is a process used to remove or delete the contents of specific cells in a dataset or spreadsheet based on a given condition or matching value. This transformation is commonly employed to clean or manipulate data by selectively clearing cells that meet certain criteria.
Here's an overview of how the Clear Cells on Matching Value data transform works:
Select a column.
Navigate to the Transforms tab.
Select the Clear Cells on Matching Value transform from the Data Cleansing category.
Operator: Select the operator required for matching from the list.
Value: The value or pattern to be searched for in the selected column.
Click the Submit option.
The value selected in the Quantity column clears the cell with 0 value.
It removes the values from the cells with 7 value and returns blanks cells:
Delete the rows on matching the condition specified for that column.
The Delete Rows on Matching Value transform is a data manipulation process used to remove rows from a dataset based on a specified condition or matching value. This transformation allows the user to selectively delete rows that meet certain criteria.
Here's an overview of how the Delete Rows on Matching Value transform works:
Select a column.
Navigate to the Transforms tab.
Select the Delete Rows on Matching Value transform from the Data Cleansing category.
Operator: Select the operator required for matching from the list.
Value: The value or pattern to be searched for in the selected column.
Click the Submit option.
The row with given value from the Sl. No. column gets deleted.
The row with 5 value has got deleted from the id column.
The Delete Rows with Empty Cell transform is a data manipulation operation commonly used in spreadsheet software or data processing tools to remove rows that contain empty cells within a specified column or across multiple columns. The transform helps clean and filter data by eliminating rows that lack essential information or have incomplete records.
Select a column.
Navigate to the Transforms tab.
Click the Delete Rows with Empty Cell transform from the Data Cleansing category.
It deletes all the rows with empty cell in that column returning the data as below:
The Delete Rows with Invalid Cell transform is a data manipulation operation used to remove rows that contain invalid or inconsistent data in a specified column or across multiple columns. This transform helps clean and filter data by eliminating rows that do not meet specific validation criteria or fail to comply with predefined rules.
Refer to the following steps on how the Delete Rows with Invalid Cell transform works:
Select a column.
Navigate to the Transforms tab.
Select the Delete Rows with Invalid Cell transform from the Data Cleansing category.
The transform deletes rows with an invalid value in the selected column.
The Delete Rows with Negative Values transform is a data manipulation operation used to remove rows that contain negative values in a specified column. This transform helps to filter and clean data by eliminating rows that have undesired negative values.
Select a column.
Navigate to the Transforms tab.
Select the Delete Rows with Negative Values transform from the Data Cleansing category.
It deletes the row with the negative value and returns the data as displayed below:
The Fill Cells with Value transform is a data manipulation operation used to replace empty or missing cells with a specified value in a column or across multiple columns. This transform helps to ensure data consistency and completeness by filling in gaps or replacing missing values.
Select a column.
Navigate to the Transforms tab.
Select the Fill Cells with Value transform from the Data Cleansing category.
Use with: Specify whether to fill with a value or another column value
Column/ Value: The value with which the column must be filled, or the column with which the value must be replaced. When the above transform is applied to the below data on the column timecol2, it fills the column with the selected value that is 30.
Click the Submit option.
It fills the selected column with a value or a value from another column.
It helps to fill the empty cells of a selected column with a value or a value from another column if the destination column is empty.
Select a column.
Navigate to the Transforms tab.
Select the Fill Empty Cells with Text transform from the Data Cleansing category.
Use with: Specify whether to fill with a value or another column value.
Column/ Value: The value with which the column must be filled, or the column with which the value must be replaced.
Click the Submit option.
When the transform is applied to the below data on the empty cell of the Bol column,
It fills the empty cell with the chosen text:
This transform adds a new Boolean column based on duplicate values in the column. For original value it gives false, and for the duplicate value, it provides true value.
Select a column.
Navigate to the Transforms tab.
Select the Flag Duplicates in Columns transform from the Data Cleansing category.
Select the column that contains duplicate values.
Click the Submit option.
It inserts a new column by flagging the duplicated values as true.
This transform adds a new Boolean column based on duplicate rows in the table. For original value it gives false, and for the duplicate value, it provides true value.
Select a column that contains duplicate values.
Navigate to the Transforms tab.
Click the Flag Duplicates in Table transform from the Data Cleansing category.
When applied on a column it inserts a new column named with the 'flag_' prefix and mentions the duplicated values as true.
The duplicated values get notified as true in the flag_duplicates_table column.
It removes duplicate values from the selected columns. This transform can be performed on a single as well as on multiple columns.
Select a column.
Navigate to the Transforms tab.
Select the Remove Duplicates from Column transform from the Data Cleansing category.
Select the column that contains duplicate values.
Click the Submit option.
The duplicated values get removed from the column.
It removes all duplicate rows from the table.​
Select a column.
Navigate to the Transforms tab.
Select the Remove Duplicates from Table transform from the Data Cleansing category.
The duplicated data from the selected column gets deleted.
It removes any letter present in the selected column. The users can either add a new column with the transformed value or overwrite the same column.
Select a column.
Navigate to the Transforms tab.
Select the Remove Letter transform from the Data Cleansing category.
Enable create new column to create a new column with the transform result.
Click the Submit option.
It removes the numbers from the newly inserted column.
It removes any number present in the selected column. We can either add a new column with the transformed value or overwrite the same column.
Select a column.
Navigate to the Transforms tab.
Select the Remove Letter transform from the Data Cleansing category.
Enable create new column to create a new column with the transform result.
Click the Submit option.
When the Remove Numbers, transform gets performed on a selected column,
It removes numbers from the selected column.
It removes any special character present in the selected column. Only letters, numbers, and spaces are retained. We can either add a new column with the transformed value or overwrite the same column.
When the transform Remove Special Characters gets performed on the selected column, the punctuations get removed from the column.
Select a column.
Navigate to the Transforms tab.
Select the Remove Special Characters transform from the Data Cleansing category.
Enable the Create new column option.
Click the Submit option.
The special character gets removed from the newly added column.
