How to remove columns with Null values?

Kunal
Kunal Contributor

Hi all, I have a dataset with 100+ columns. There are many columns with null values. I would like to remove all those columns without manually selecting those columns using the Select Columns tile on Magic ETL. Is there a way to do this?

Comments

  • @Kunal

    You could try using a Dynamic Unpivot Tile, then a filter tile to remove any rows with a null value and then pivot the remaining data again.

    You could also look into creating a dataset view of the data. When you are looking at the "Overview" for a dataset, you will see a section called "Data Profiler". This section will provide you with some recommended actions:

    If you click on "VIEW ISSUE", you can create a view of your dataset that will remove any columns that are null:

    Then just use this view as the input for your ETL.

    The advantage to using the unpivot/pivot approach would be that it would be dynamic, meaning that if data did come through in one of the fields, the field would no longer be deleted. However, once you remove the field using the dataset view, then that view will never contain the field.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Kunal
    Kunal Contributor

    Thanks, Superman ! :D This was helpful