Remove Records

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!

Preparation

Accepted answers

mceleavey

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,

TheOC

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

All comments

mceleavey

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,

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.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703