How to check all items in child array are present in parent array inside data flow?

Achu A 30 Reputation points
2023-07-21T04:01:32.66+00:00

For eg:

ParentArray: [A,B,C,D,E]

ChildArray: [D,E]

I need to check all items in ChildArray are present in ParentArray inside mapping data flow.

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

Accepted answer
  1. Suba Balaji 10,931 Reputation points
    2023-07-21T05:12:40.25+00:00

    Hi Achu A

    Welcome to MS Q&A platform and for posting your query.

    You can use the below expression to check if all elements from child array are there in the parent array

    size(except($child,intersect($parent,$child)))
    
    
    

    In the above expression, parent and child are array parameters in data flow.

    1. Intersect finds out matching elements between child and parent. after finding matching elements, we are comparing the matched elements back with child to verify if there is no difference
    2. The above expression would result in 0 if child elements are there in parent and a non zero number if some elements in child are missing in parent.
    3. this can be used in derived column transformation, and captured in a new derived column called size.
    4. Following the derived column, you can have your logic to check size. if size>0, then some elements are missing and if size=0, child elements are there in parent too.

    Hope it helps.


0 additional answers

Sort by: Most helpful