Dynamic Value Filtering in Data Flow seeking Flexible Solutions

Question

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.

Answer 1

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)

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.