Synapse Dataflow Lookup for multiple conditions with OR

Question

Synapse Dataflow Lookup for multiple conditions with OR

Edward Coltman 20

I am building a pipeline in Azure Synapse. Within one of my dataflows I am introducing a lookup function. I am using two key names to perform the lookup. The json field looks a bit like this:

... lookup(Source@incoming_name_a == target_name_a",
                "     && Source@incoming_name_b == target_name_b,",
                "     multiple: false,",
                "     pickup: 'any',",
                "     broadcast: 'auto')~> LookupNames", ...

I need to adjust this such that the lookup will match on either name_a OR name_b.
Which could look like this:

"... lookup(Source@incoming_name_a == target_name_a",
    "     || Source@incoming_name_b == target_name_b,",

This should be simple, but there are no options for adding this logic between the multiple conditions in the lookup dataflow activity. How can I do this? I have tried to directly edit the json and the dataflow script, but these changes are not being accepted.

J N S S Kasyap 3,780 Reputation points Microsoft External Staff Moderator

2025-04-17T16:21:46.5933333+00:00

Just checking in to see if the below answer provided by @Vinodh247 helped.

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.

1 answer

Your answer

J N S S Kasyap 3,780 Reputation points Microsoft External Staff Moderator

2025-04-17T16:21:46.5933333+00:00

Just checking in to see if the below answer provided by @Vinodh247 helped.

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.

Answer 1

Vinodh247 34,666 MVP Volunteer Moderator

Hi ,

Thanks for reaching out to Microsoft Q&A.

In synapse data flows (also known as mapping data flows), the lookup transformation only supports AND (&&) conditions between match keys, it does not support OR (||) logic directly.

This limitation means your attempted JSON/script update with || will not be accepted or function as expected. You cannot do a logical OR across match conditions within a single Lookup transformation

Approach 1: Use Two Lookups and Union

Create two separate Lookup transformations:
- One that joins on incoming_name_a == target_name_a
- Another that joins on incoming_name_b == target_name_b
Use a Union transformation to combine the outputs of the two Lookups.
Optionally add a Derived Column or Conditional Split if you need to de-duplicate or add metadata about which match condition was met.

Approach 2: Prejoin Using Derived Columns

If you want to avoid two lookups, another method is:

Create a Derived Column that combines the 2incoming names into a single field with a known priority or structure.

Use this derived field as a single match key in your Lookup.

But this only works if the match logic can be standardized (a coalesce or priority-based name selection)

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

Edward Coltman 20 Reputation points

2025-04-15T09:25:22.5766667+00:00
Thank you for this input. Can you explain further the use of coalesce? From what I understand this is only an option if one of the fields is null. This is not the case.

name_a does have a priority, and if the lookup does not manage to match on name_a, name_b can also be tested. Is it possible to manage the OR with this?

... lookup(coalesce(left@name_a, left@name_b) == coalese(right@name_a, right@name_b),",

How can I manage this?
Vinodh247 34,666 Reputation points MVP Volunteer Moderator

2025-04-15T16:59:39.0366667+00:00
right! coalesce() works only when one or more of the values might be null, as it returns the first non-null value in the list.

So if both name_a and name_b are non-null, but you want to prioritize name_a for matching, and only fall back to name_b if name_a does not result in a match, then coalesce() is not the right tool for this logic.

coalesce() is only useful when you are dealing with possible nulls and want to pick the first available value.

For fallback matching (name_a if possible, else name_b), use two lookups and conditional logic.

There is no native OR logic in lookup join keys in synapse data flows.
J N S S Kasyap 3,780 Reputation points Microsoft External Staff Moderator

2025-04-16T16:44:22.8666667+00:00

@Edward Coltman
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
J N S S Kasyap 3,780 Reputation points Microsoft External Staff Moderator

2025-04-18T17:05:14.4266667+00:00

Just checking in to see if the above answer provided by @Vinodh247 helped.

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.

Share via

Synapse Dataflow Lookup for multiple conditions with OR

1 answer

Your answer