Remove Records

[Schu19]

Hello, 

I'm running into an issue where once I've consolidated a few excel files, I end up with records that contain values of N/A, 0, or are Blank across the entire record, is there an easy way to remove these records from the data set prior to bringing into excel? They won't be consistent records that return these values each month so I'm hoping for a tool/process that will scan the consolidated data and remove all of the records that contain either N/A, 0, or blanks each month. For reference, one record/row may have all three values, or only 1 or 2, but they don't contain any data I need so would like to have removed. 

Thanks!

[mceleavey · Accepted solution]

Hi @Schu19 ,

there are a few ways depending on your data.

1. Apply a Data Cleansing tool and remove null rows

2. Apply a filter and remove where a key field or fields are empty

3. More complex but might suit: Use the transpose tool grouped by your key field. Apply a filter and remove any empty fields from your value column and pivot back using crosstab.

Hope this helps,

M.

[TheOC]

hey @Schu19 

Exactly as @mceleavey said, there are multiple ways to do this.

My instinct would be the filter tool. Given a mock setup:

I can apply the following filter:

And if any are null, they will drop out of the F anchor of the filter, leaving me with my data in the T anchor:

I've attached this workflow if it helps.

Cheers,
TheOC

[Schu19]

Thanks Mceleavey, 

I've tried removing null rows, however that retains the N/A values and 0 value records. 

Applying a filter requires me to apply an individual filter to each of the 15 columns so that I don't mistakenly remove data I need to keep, i.e. one column may be 0, however there is still data in the record I need, example Q1 is 0, however Q2 and Q3 contain needed values. 

I'm looking for a way to say, if the entire record across is Null, 0, blank, or N/A, then it can be removed, but if there is anything else in the record/row, keep the entire row"

Thanks. 

[Schu19]

Thanks TheOC, 

In your below example, I would still want to keep all of the records data, as some of the columns have required data,( ex. record 7, if though it has a null value in column 1, there is data in the other columns). I only want to remove the record/row if the entire record/row is null/blank/0/N/A. If the row contains anything other than those values, I'd want to keep it. 

[TheOC]

hey @Schu19 

I think this should solve it for you.

The only potential issue, is it replaces "N/A" with blank by the end of the workflow, 0's are converted back to 0's so that shouldn't be an issue.

If a numeric contains a 0, I'm making it null:

and if a string field contains "N/A" or empty, im making it null.

Then, as @mceleavey said with the data cleanse tool, removing null rows, and then replacing null with 0 for numerics and blank for strings:

I hope this helps!
TheOC

[Schu19]

So, I think we're very close, I've made all numerics that are 0 null, how do I make all text/vstring fields that are blank null as well? Sorry, fairly new at this. 

Still running into these in my data because I think text fields that are blank aren't registering as null for the final data cleanse. 

Thanks!

[TheOC]

hey @Schu19 

Thats perfect!
However, a multi-field formula tool can only change one type of data at once.
In this case, numeric. 
So i put a new Multi-field formula tool on, to convert both 'N/A', and '' in text fields, to null.

As in the attached workflow/screenshot below:

make sure to change the dropdown in the top left of the configuration window to text fields, and you will want the following formula:
IIF([_CurrentField_] = "N/A" OR [_CurrentField_] = "", null(),[_CurrentField_])


Hope this helps!
TheOC

[Schu19]

Awesome! Added a few more Multi-Row formulas and have the result I was hoping for, thanks!!

[TheOC · Accepted solution]

Perfect! Glad you got it sorted!

Please feel free to accept mine and @mceleavey 's answer as solutions, to help people in the future, and give me a shout (my private messages are open, or this thread) if you need any further help!

Cheers!
TheOC