Remove Empty Columns without Select Tool

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!

RemoveEmptyColumns.PNG

Workflow

Accepted answers

MarqueeCrew

@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

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

Non empty.yxmd

kat

@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!

All comments

MarqueeCrew

@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

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

Non empty.yxmd

kat

@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

DanHare

Just in case anyone gets past the w1lly waving... 😃

I'd like to add a round of applause for this macro.

It's brilliant for when you are pulling data straight in from Salesforce, CRM, Xero etc.

Great work Mark !

TonyAdam

I had a similar challenge. I also wanted to remove Empty or NULL columns from my data set. This is how I found this thread. But additionally I wanted to remove all columns that have the same value in all rows and by that don't provide any value. I played around with the Basic Data Profile Tool and build a macro around it. When I tested it, I noticed that it could also easily remove the Empty/NULL columns. So it solves both of my issues in one step and it is quite simple and fast 😄

I also shared it in the Gallery: https://gallery.alteryx.com/#!app/Remove-useless-columns/5e6acbbd0462d70dd8d41d1b

Thanks for the inspiration from your macro @MarqueeCrew

Tony

Remove useless columns.yxmc

jhaines

With the Multi-Field Formula tool, Select "All Types of" fields and then in the expression window put this formula:

IF IsEmpty([_CurrentField_])
THEN Null()
ELSE [_CurrentField_]
ENDIF

Then follow up the Multi-Field Formula with the Data Cleansing tool configured to Remove null columns.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703