Remove zero values

[vivian123]

Is there a faster way to replace all the zero values in multiple columns with null/blank cell? I used filter function but there are more than 40 columns I need to do this for. 

Example Input table

StudentID	Week 1 grade	Week 2 grade	Week 3 grade	Week 4 grade	Week 5 grade
1	10	0	6
2	0	5			2
3		2	6	0	0

Output table

StudentID	Week 1 grade	Week 2 grade	Week 3 grade	Week 4 grade	Week 5 grade
1	10		6
2		5			2
3		2	6

Thanks

[tonypreece]

Hi @vivian123 , there is an easy way to do this using a transpose/crosstab method.

Transpose puts all your data into a single column, you can then use a formula on that column to replace zeros with nulls and finally crosstab back into the original format.  I've added a Select tool in here to make sure the 'Value' field containing the main data is set to Integer rather than string.

[Qiu · Accepted solution]

@vivian123 
We can also use a Multiple Column tool

[DawnDuong]

hi @vivian123 

For such data cleansing tasks, there are 2 considerations:

- WHERE: the rules to decide which columns should be cleansed

- WHAT: the rules to decide the modifications (change from WHAT to WHAT).

When you want to apply the same logic to all columns (e.g. rename column name, data cleansing etc...) or columns whose names/formats follow certain patterns (I.e. determining WHERE is simple and WHAT is the same for all WHERE), you should immediately think of the Multi-field tool.

When you want to apply different logic to different fields depending on both the field name and the field values (i.e. WHAT and WHERE are interdependent), the safer choice is to do the following steps:

1) add unique ID to record (so that you can match the original results later)

2) Transpose so that you have the field name and field values in the data for further processing.

3) Apply the rules to get the new values (typically with FORMULA tool)

4) Crosstab to get back the original data format.

Cheers,

Dawn.