Share via

How to look up values in another table based on multiple columns?

AE90 60 Reputation points
2025-08-05T17:41:38.61+00:00

I have a dataset that needs to reference another table to look up and replace values. I'm not quite sure how to set this up in the data flow, since there's no common columns in the dataset or reference tables. There are multiple columns in the dataset that use the reference table. Each column needs to match on the TABLE96_EHR_VALUE and its corresponding DISCRIMINATOR. This image shows the relationship needed to get the replacing values:

User's image

I know how to use the derived column to replace values, but I can't figure out how to set up the relationship to the reference table to get them.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
Accepted answer
  1. Amira Bedhiafi 35,766 Reputation points Volunteer Moderator
    2025-08-05T21:09:34.82+00:00

    Hello !

    Thank you for posting on Microsoft Learn.

    You need to use 2 sources :

    • Source 1: your main dataset (original dataset)
    • Source 2: the reference table (with columns like TABLE96_EHR_VALUE, TABLE96_DISCRIMINATOR, and TABLE96_CODE)

    Then you'll need three separate lookup transformations, one for each discriminator:

    • a lookup for FUNDINGTYPE with Discriminator = "B"
    OriginalDataset.FUNDINGTYPE == Reference.TABLE96_EHR_VALUE

    and use a derived column in reference source or filter before join:

    TABLE96_DISCRIMINATOR == "B"

    Bring in TABLE96_CODE as FUNDINGTYPE_CODE (as an output column).

    • a lookup for GSDWAIVER with Discriminator = "G"
    OriginalDataset.GSDWAIVER == Reference.TABLE96_EHR_VALUE

    and use a derived column in reference source or filter before join:

    TABLE96_DISCRIMINATOR == "G"

    Then the output column should be GSDWAIVER_CODE.

    • finally a lookup for FUNDINGSOURCE with Discriminator = "W"
    OriginalDataset.FUNDINGSOURCE == Reference.TABLE96_EHR_VALUE

    and use a derived column in reference source or filter before join:

    TABLE96_DISCRIMINATOR == "W"

    Then the output column should be FUNDINGSOURCE_CODE

    Each lookup adds a new column (_CODE) with the corresponding TABLE96_CODE. These values now replace or enrich your main dataset.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.