How to get null value for no value in ADF DataFlow

Question

Normal output post transformation as attached.

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}

Answer 1

Hello @Abhijit Bhadra ,

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 or upvote 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