Filtering data Dynamically

[Alteryxexpert]

I have Data Like Below and I need to write output excluding the columns which has no data, In this case Columns till 31-Mar-2024 (Excluding all the null columns after 31-Mar-2024 columns), and the same flow should write output of all columns when data is available in future executions. 

Item	Profit Split %	31-Mar-24	30-Apr-24	31-May-24	30-Jun-24
Chair	14.31%	28508873.05
Pen	7.03%	13998997.02
Table	11.93%	23757394.21
Mouse	66.74%	132946378
Keyboard	-14.09%	-145000000

I have tried using data cleansing , but it is not dynamic, when new columns are added it won't pick, Is there a way to achieve this?

Current Required output:

Item	Profit Split %	31-Mar-24
Chair	14.31%	28508873.05
Pen	7.03%	13998997.02
Table	11.93%	23757394.21
Mouse	66.74%	132946378
Keyboard	-14.09%	-145000000

Future require output when data is available:

Item	Profit Split %	31-Mar-24	30-Apr-24	31-May-24	30-Jun-24
Chair	14.31%	28508873.05	4545	645666	69355
Pen	7.03%	13998997.02	2545	54654	6446596
Table	11.93%	23757394.21	15563	54684	89589589
Mouse	66.74%	132946378	54656	516555	89899
Keyboard	-14.09%	-145000000	5656	21625	454545

[cjaneczko]

You can use a Transpose, filter and crosstab to accomplish this.

[peterr_h]

Hi,

The above solution works, but if all you need is to remove Null columns, the Data Cleansing tool has this built in.

This will work dynamically.

Please let me know if this doesn't help in your case. You may need to set 'blank' (empty) cells to Null() before in some cases, but this should work given the data shown.

Cheers

[Alteryxexpert]

Is there a n other way rather then using transpose and data cleansing? Using both will extent the workflow so don't want that to happen, let me know if there is an other way like using multi field formula tool

[cjaneczko]

What do you mean by extending the workflow?

[Raj]

@Alteryxexpert are you trying to achieve it by single tool?
while you say not to increase the length of workflow.

[peterr_h]

Hi @Alteryxexpert, I added a solution above which uses the Data Cleansing tool, performing the task in one tool. Does that work for you?