Removing columns with entirely null values in ADF dataflow

Walter Pelowski 66 Reputation points
2023-05-18T18:22:19.19+00:00

Is there a way to remove entirely blank columns in an Azure Data Factory dataflow? I can't figure out a Select transformation that would allow me to select columns that only have data in them and ignore those that don't have data.

Alternately, I'd like to split/flatten a column horizontally based upon a specific delimiter. I have composite columns that I'd like to dynamically split based on something like a colon, but I'd like my dataflow to support a dynamic number of delimited values. Right now I'm doing the following which is inflexible and forces me to remove the columns with data values that do not exist.

https://app.screencast.com/2P9obXl2nrvbc

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,338 questions
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 30,101 Reputation points Microsoft Employee
    2023-05-22T06:12:22.37+00:00

    Hi Walter Pelowski ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your query, you want to remove the column entirely if all the records are null in that column with the help of mapping dataflow. Please let me know if that is not the correct understanding of your requirement.

    Unfortunately, there is no direct way of achieving this in dataflow. Using rule based mapping in select transformation would allow us to remove a column based on its name or type , but not based on its column values.

    Upon checking, All the suggestions I got was regarding replacing Null value to 'Unknown', the closest approach I could find is using combination of ADF activities and dataflow here: Dropping columns in Azure Data Factory based on values in columns . Please check if it helps.

    Other way is to use stored procedure activity in ADF: How to detect and remove a column that contains only null values?

    Kindly accept the answer by clicking on Accept answer button. Thankyou


0 additional answers

Sort by: Most helpful