Search code examples
azureazure-data-factoryazure-databricksazure-data-lake-gen2

How to group by the empty cell col ,also in the final result of aggregation in Azure Data Flow

I have 3 cols in my csv file

enter image description here

In group by section i mentioned : col1

At Aggregate ,I mentioned sum(col2)

Output i got like in Azure dataflow Aggregation transformation :

enter image description here But My requirement is to include the empty valued col1 cell as one group like:

enter image description here

How Can I achieve this in Data Flow, I can manually fill up the empty cell with some rand value,but I dont want to mess the Client data .

Solution

  • I tried the same with your input data and its working fine for me.

    Input data:

    col1,col2,col3
,12.24,2022-04-07
2,13.34,2022-04-08
2,14.45,2022-04-09
3,14.33,2022-05-03
3,14.55,2022-05-10

    In the mapping, I have set to string and double for col1 and col2 respectively.

    enter image description here

    For the empty cell, it took it as NULL value and it gave me proper results after aggregation.

    enter image description here

    After transforming the data to sink csv file, it gave the NULL as empty cell.

    enter image description here

    I tried with multiple rows with empty cells and it's working fine for me. Also, I had tried by converting the NUlls to empty strings with this expression iif(isNull(col1), '', toString(col1))) in derived column and it is working fine for empty strings(inplace of NULL) as well.

    I suggest you try trouble shooting steps like executing after publishing and close ADF and try again after some time. If it results the same, then it's better to raise a Support request on this issue for deeper investigation.