Organizing Table with many nulls and unnecessary rows

[Csam]

Good morning,

I have a large table with many columns which all contain the same type of row info but with different values. Currently there's a lot of nulls which makes my table much larger than it needs to be. Here's an example of what I mean:

COLOUR	SHIRT QUANTITY	PANTS QUANTITY	SHOES QUANTITY
RED	43	NULL	NULL
BLUE	54	NULL	NULL
RED	NULL	23	NULL
BLUE	NULL	45	NULL
RED	NULL	NULL	65
BLUE	NULL	NULL	34

When I would like it to be:

COLOUR	SHIRT QUANTITY	PANTS QUANTITY	SHOES QUANTITY
RED	43	23	65
BLUE	54	45	34

Is there a way to easily transform the first table into the second in Alteryx?

Thank you!

[Thableaus · Accepted solution]

Hi @Csam 

There's a little trick using Transpose Tool and the Cross-Tab tool that can help you do that.

- Transpose your columns grouping by Colour field

- Filter the Null Values 

- Use Cross-Tab tool to go back to your dataset

You might need to rename and sort the columns, but I think this works pretty well.

WF attached.

Cheers,