Removing Null from multiple columns

[ksarker]

Hello, 

i am working with multiple Quarters YTD data. how can i remove line C and E as there are no $$. Also, how can i convert 0 to Null to be consistent with Quarters with Null

Business	Q1	Q2	Q3	Q4
A	20	-15	Null	10
B	12	Null	-20	50
C	Null	Null	0	Null
D	-50	10	40	Null
E	Null	Null	Null	0

[Luke_C]

Hi @ksarker 

Here's one way:

1. Multi-field to update 0 to null
2. Transpose quarter info
3. Summarize tool to count records per business and null records
4. Compare record count to null record count and filter out businesses where all records are null
5. Join back to remove data

[IraWatt · Accepted solution]

Hey @ksarker,

One way of doing this is with a multi field formula like this:

That formula will replace all 0's with NULL. 

Then a Filter tool can get rid of the NULL rows:

Any questions or issues please ask :)
HTH!
Ira

[DylanDowrick]

Here is an updated macro from the data cleanse tool which will automatically filter out Null/Zero Rows!

[ksarker]

Awesome !!!!!!. these steps work perfectly. thanks for your help.  

[IraWatt]

Great to hear @ksarker 😄