Remove Empty Columns without Select Tool

[Chirag_Gandhi07]

Hi everyone,

How can I remove the empty columns (they are empty, not null) without having to use the select tool. I am pretty sure this can be done with transpose and cross tab tools but I am not entirely sure how. I have attached a screenshot of some data. I would like to remove the columns Title 5, Title 6, and Title 7. Any help would be much appreciated.

Thank you!

[MarqueeCrew · Accepted solution]

@Chirag_Gandhi07,

I have published a macro to delete fields that are NULL/Empty.  It gives you the option to select/avoid any fields that you either care or don't care about.

https://gallery.alteryx.com/#!app/CReW-Delete-Empty-Fields/5ab9138a0462d71894a25060

Cheers,

Mark

[jdunkerley79 · Accepted solution]

Fun challenge.

My approach:

- Transpose the data

- Filter out the empty or null cells

- Pick a unique set of names

- Cross tab back and repair the names back to original (using a dynamic formula)

- Use a union tool set on common subset on the original stream

Sample attached

[kat · Accepted solution]

@MarqueeCrew - from what I read @Chirag_Gandhi07 just wanted emptys to be removed, not nulls. I had this too before and just changed the tool a tiny bit.

I changed the formula on the shrink to be

IIF(IsNull([_CurrentField_]),"1",IIF(IsEmpty([_CurrentField_]),null(),"1"))

It does what it should, but do you think it's the best way?

Great tool by the way! :)

[MarqueeCrew]

@Chirag_Gandhi07,

Don't be distracted from the simplicity of a CReW macro designed with you in mind by the evil @jdunkerley79.  He might be flashing a bright and shiny object your way trying to distract you in a vain attempt to gain Stars and Solution badges.  

Cheers,

Mark

[jdunkerley79]

Yep @MarqueeCrew I always want the badges. The CReW macros are always a good addition.

If you want to keep NULLS as @kat suggests then change the expression to:

!IsEmpty([Value]) OR IsNull([Value])

[MarqueeCrew]

@kat,

It looks like you might have your preferences set so that I can't mention you.  Empty/Null is good feedback.  I've updated the gallery macro.

Is it the best way?  There is a suggested alternative that does not transpose the data.  I think that the case where performance is needed makes the macro a challenge to understand.  So for general purposes I stand behind the macro.

Cheers,

Mark

[MarqueeCrew]

@jdunkerley79,

Here we go again!  Always trying to shine your "Expert" Certification knowledge in my "Advanced" direction.  I think that your reply is a trick.  The function of IsEmpty() looks for both Null and Empty conditions (e.g. [Null] or \0, and "").

[Privately to James:  Now we've really confused the author.  Watch!  Kat will get the solve as a protest to our hijacking his post for our amusement]

Cheers,

Mark

[kat]

@MarqueeCrew I agree with the macro - just meant the way I changed it. 

Can't see the change in the gallery though?

[kat]

congratulations on the expert certification @jdunkerley79! that's amazing!