Extracting Nulls and Blanks

Monarch Data Prep Studio allows you to easily eliminate all empty rows in your table.

For instance you start with a table like this:

 

And remove empty rows to have a table like this:

 

Steps:

1. Go to the Prepare window.

2. Select the table you want to transform.

3. Select Transform on the Monarch Data Prep Studio Toolbar.

A dialog box allows you to select a transformation.

4. Select Extract Rows.

The Extract Rows dialog displays.



 

5. Select the action you want performed. You can:

   • Exclude empty rows - Remove all rows with empty values in all columns

   • Exclude rows where there is an empty value in any column - Remove all rows with at least one empty value in any column

   • Include rows where there is an empty value in any column - Retain ONLY rows with at least one empty value in any column

6. Select column criteria. Click one or all of the following:

   • No content: Represents a value that is intentionally left blank.
     For example, the column Spouse Name may be left blank for an individual who is not married.

   • Null: Indicates a value that does not exist at all.

   • Whitespace Only - Represents a value of white spaces (text strings)

7. Select the columns against which the criteria will be evaluated:

• Use All Columns

• Use Selected Columns. If you select this option you must select columns from the list.

8. Click on a key icon beside a column to select it. You can use the Search Column box to easily look for the columns you want to include.

9. Click OK when you are finished.

Monarch Data Prep Studio removes empty records and creates a new table:



 

The new table is named: <TransformationApplied>, in this case "ExtractRows." You can rename this table as needed.