Lookup operation in Dataflow returns Nulls

eugenia apostolopoulou 76 Reputation points
2022-03-29T15:54:19.537+00:00

Hello,

I'm trying to create a dataset combining two sql db sources, lets say source 1 and source 2. In my sources I can see that I do not have any nulls in any column (I checked it using the statistics option in azure data preview as well as in my db tables using sql query). Having set my sources, I added a Lookup operation in order to bring the columns of source 2 in source 1. However, in data preview of the Lookup I saw that all the columns of the source 2 have null values. I run an sql query in db where I executed a left outer join between the two sources (using the same fields I have used in Lookup's conditions) and it returned values (I didn't have any nulls in my sql query results). I have spent a lot of time trying to find out why this is happening. Any ideas, please?

Thanks in advance!

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

2 answers

Sort by: Most helpful
  1. AnnuKumari-MSFT 31,731 Reputation points Microsoft Employee
    2022-03-31T06:16:48.563+00:00

    Hi @eugenia apostolopoulou ,

    Thankyou for using Microsoft Q&A platform and posting your query.

    Looking at your question, it seems that you are trying to make use of LookUp transformation in data flow in order to reference data from two sources based on the matched columns. However, the resulted output contains NULL values which is not expected result. Please let me know if my understanding about your query is incorrect.

    Kindly check whether the schema for both the sources are imported correctly .

    Also, Please help us with the screenshots of lookup transformation configurations you have set to provide better help.


  2. VC 51 Reputation points
    2023-02-14T11:56:49.65+00:00

    Hi @Annu Kumari ,

    I had the same issue like the main question here. In my case lookup is failing for only one record. Ex: I had 4 records in a columns, I am trying to lookup a value based on those 4 records column. I am getting 3 records correctly, but 1 record is coming as Null. Used cast in sql, substring in adf dataflow functions till now.

    value lkp_value

    1 1

    2 Null

    3 3

    4 4

    Can you help with this ?