Dynamically selecting columns which has values not equal to 0

[Lumjing]

Hi I have a data set and below is a sample of it. What I am trying to do here is compare column A and B and update C is A and B is not matching. I want an out put where It should should only columns where C values are not equal to 0. I have about 30 columns and in this 30 check columns I want only the check columns where the output value is not equal to 0. From the below sample table I want only column D,E,DE_Check and H,I,HI_Check should only show up as DE_Check and HI_Check where rows is not equal to zero. this is like a check that I am planning to product. Thanks for the help 

S/No	A	B	AB_Check	D	E	DE_Check	H	I	HI_Check	F	G	FG_Check	L	M	FG_Check
1	1	1	0	3	1	1	2	2	0	1	1	0	1	1	0
2	2	2	0	1	1	0	1	2	1	2	2	0	2	2	0
3	3	3	0	2	3	1	3	2	1	3	3	0	3	3	0

[OllieClarke · Accepted solution]

Hi @Lumjing 

Here's one way of doing this:

We can transpose the data to then find the check columns that we want. We then parse from the names of those columns, the base column names, and use that list to join as a filter to get all the columns we want. Then we can cross-tab to go back to the original structure.

This will return all the rows which for those columns. If you only want the columns where there is a non-0 check value then let me know and I can show you how to edit the workflow to do that.

Hope that helps,

Ollie

[binu_acs]

@Lumjing another approach

[flying008]

Hi, @Lumjing 

Another dynamic resolution for you.

Spoiler

IF Contains([Name], '_Check') && [Sum_Value] > 0 
THEN [Name]
ELSEIF (Contains([Row+1:Name], '_Check') && [Row+1:Sum_Value] > 0) || (Contains([Row+2:Name], '_Check') && [Row+2:Sum_Value] > 0)  
THEN [Name]
ELSE '0_' + [Name]
ENDIF

S/No	D	E	DE_Check	H	I	HI_Check
1	3	1	1	2	2	0
2	1	1	0	1	2	1
3	2	3	1	3	2	1

[Lumjing]

@OllieClarke It worked. Thank you so much for the quick solution