Dynamic Value Filtering in Data Flow seeking Flexible Solutions

Clover J 200 Reputation points
2023-11-23T20:00:43.9566667+00:00

I want to filter a data flow column based on values from a SQL query like SELECT * FROM my_delta_table where ptf_proc_link_id in (A, B, C). The challenge is that data flow documentation only mentions filtering with single values (i.e., ptf_proc_link_id = A). I've tried using intersect, but both parameters need to be arrays, and one is a column in my case. While I could manually specify ptf_proc_link_id = A OR ptf_proc_link_id = B OR ptf_proc_link_id = C, I don't know in advance how many values I'll have. Seeking guidance on a more flexible approach for this common data flow scenario.

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

Accepted answer
  1. Smaran Thoomu 12,445 Reputation points Microsoft Vendor
    2023-11-24T10:52:36.6+00:00

    Hi @Clover J

    Thanks for reaching out to the community forum.

    As I understand you're looking for a more flexible approach to filter a data flow column based on values from a SQL query, as the documentation only mentions filtering with single values. If this is not, please let me know.

    I would like to suggest using the "IN" operator in the filter expression of the data flow column. The "IN" operator allows you to filter a column based on multiple values. Here's an example of how to use the "IN" operator in the filter expression:

    in(array_of_items, item_to_find)
    

    I tried to reproduce this with sample data. In the GIF below, you can see how to achieve your requirement.

    • I tried to filter the records with key = 'OH','TX','WA' as below expression
    in(['TX', 'WA','OH'], stateProvince)
    

    Animations2g

    Reference: Microsoft document on data flow expression - in

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.


0 additional answers

Sort by: Most helpful