question

AbhijitBhadra-3556 avatar image
1 Vote"
AbhijitBhadra-3556 asked AbhijitBhadra-3556 commented

How to get null value for no value in ADF DataFlow


Normal output post transformation as attached.

204706-image.png
Using "iifNull(PPM, 'null', PPM)" expression getting below output, where null returns as "null"
{"PPM":"RUD"}
{"PPM":"null"}
{"PPM":"PRA"}
{"PPM":"null"}
But requirement is as below, want to get null instead of "null". is there any possibility to get the same?
{"PPM":"RUD"}
{"PPM":null}
{"PPM":"PRA"}
{"PPM":null}





azure-data-factory
image.png (3.2 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AbhijitBhadra-3556,

Welcome to the Microsoft Q&A platform.

To better assist on your query, could you please confirm what is your source and sink data format (is it csv text/Json). Also if possible could you please share a sample source data (Dummy data) and expected output file with data format required.

If your sink is JSON file, then are you expecting your output JSON data to be like below?

204923-image.png


0 Votes 0 ·
image.png (3.8 KiB)

Hello Kranthi,

I have Azure SQL DB as source & output should be in JSON format like you shared the JSON format.

Source
204907-image.png


0 Votes 0 ·
image.png (2.1 KiB)

1 Answer

KranthiPakala-MSFT avatar image
1 Vote"
KranthiPakala-MSFT answered OmarAbdelBari-8959 commented

Hello @AbhijitBhadra-3556,

Thanks for confirming.

I tried multiple ways but no luck and finally reached out to product team to check if it is really possible to write JSON Null into sink data store of JSON Format and confirmed that it is not possible currently using Mapping data flow and product team is evaluating on adding a feature implementation to handle this in near future, but we do not have an ETA at this time.

This is possible if we use copy activity wherein we can have null in json for null value in your source data store, but seems it's not possible in Dataflows..

Current available options are either we can go with empty string or string null and then look a work around based on requirement or if your requirement can be met using copy activity then use Copy activity instead as it supports writing JSON null.

I would also encourage you to please log feature request in IDEAS forum here : https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c
Product team will closely monitor all feedbacks and try to implement them in future releases. Logging a feature request and sharing the feedback link here will also help other community members to upvote and comment on your feedback to help increase the priority of the feature request.

Hope this info will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Kranthi,

Thanks for you suggestion.
As you suggested non string null value can achieved using copy activity, but in my requirement DataFlow activity required as well and after copy activity once those null values passes through DataFlow it's again reflected as empty string so as of now looking forward to get non string null value using DataFlow.

0 Votes 0 ·