Filtering selected (and dynamic) rows that contain null values - except one

[datauser4234]

Hello,

I would like to be able to retain the original RecordID of all items from my input but also delete (or filter) all null rows, including consideration of columns that may not be included currently.

For example - In illustration 1 I have an input where rows 5 and 7 are Null. I would like to filter (or retain) only rows that are non-null. I can do this using a filter easily. But in my workflow the inputs are dynamic and the columns headers are not always consistent. Therefore, I would like to retain the RecordID but filter out or delete any rows that are null (not considering the RecordID column). See Illustration 2 for wanted results

Has anyone come across a solution to this? Thanks in advance! 

Illustration 1

Illustration 2

[Muhai · Accepted solution]

Hi @datauser4234,

You may use transpose and crosstab as solution.

Hope this works.

[IraWatt]

Hey @datauser4234,

To apply filtering to a dynamic number of columns, transpose is usually the way:

I think this workflow achieves what you want.

Any questions or issues please ask :)
HTH!
Ira

[datauser4234]

Thanks, this worked great!!!

[datauser4234]

I just noticed that once it Cross tabs, it adds an underscore to any field names that had spaces. For example, if you had labeled the "Revenue" field as "Revenue ID" it would have renamed it to "Revenue_ID" after the cross tab. Is there anyway to retain the original naming convention?

[IraWatt]

Hey @datauser4234,

you can use the dynamic rename tool to replace the _ with spaces:

[datauser4234]

This works great but if there is an instance that someone enters titles with underscores originally and then the dynamic rename tool would not work appropriately in that instance. Any ideas for that scenario? 

Sorry for throwing more questions out!

[IraWatt]

Ah that is a good question. For that I would use this workflow:

The sample takes the headers at the beginning. Then union on position at the end which gives the original headers

[datauser4234]

The issue that comes up when going this route is that the input files are going to be dynamic and it's uncertain which ones might be null columns (if any). Another issue that came up was that the ordering came out different. I was trying to incorporate the "Field Info" tool and rename it by parsing out the details from the Source field but am having some trouble with it. Any other suggestions in these instances?

Thank you again for your input!!

[IraWatt]

@datauser4234 that should be easy enough just select the common subset option in the union here:

Any dropped columns wont come through.