Getting Null in lookup transformation data flow

Question

Getting Null in lookup transformation data flow

Haider, Mustafa (Reigate) 25

I have got two sources Source A contains AccountGroupName and Source B contains AccountGroupName and ID, I have created a lookup transformation and used AccountGroupName = AccountGroupName to get the ID which i want to insert to destionation.

e.g. AccountGroupName = ABC exists in both Source A and Source B so I should be getting the ID in the data flow to insert to destionation, but I am getting null. Do not understand why.

Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-10-11T19:00:07.39+00:00
Hello Haider, Mustafa (Reigate),

Welcome to the Microsoft Q&A forum.

Can you please check the below and see if it helps?

Ensure the AccountGroupName column in both sources has the same data type and length.

Check for any leading or trailing spaces in the AccountGroupName column in either source. If there are, the lookup condition may not match the data correctly. You can use the TRIM function to remove any leading or trailing spaces.

Verify that the AccountGroupName column in both sources has the same case sensitivity. If one source has uppercase letters and the other has lowercase letters, the lookup condition may not match the data correctly. You can use the UPPER or LOWER function to convert the case of the data.

Check for any null values in the AccountGroupName column in either source. If there are, the lookup condition may not match the data correctly. You can use the ISNULL function to replace any null values with a default value.

Use data preview to inspect the data at different stages of your ETL process to see if the values in Source A and Source B are as expected and if the lookup transformation is working correctly.

I hope this helps.
Haider, Mustafa (Reigate) 25 Reputation points

2023-10-12T10:15:06.4733333+00:00

Trying above suggestions will update as a answer after doing complete testing in couple of days
Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-10-12T16:20:28.24+00:00

Sure. Thank you, Haider, Mustafa (Reigate)

Accepted answer

1 additional answer

Your answer

Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-10-11T19:00:07.39+00:00

Hello Haider, Mustafa (Reigate),

Welcome to the Microsoft Q&A forum.

Can you please check the below and see if it helps?

Ensure the AccountGroupName column in both sources has the same data type and length.

Check for any leading or trailing spaces in the AccountGroupName column in either source. If there are, the lookup condition may not match the data correctly. You can use the TRIM function to remove any leading or trailing spaces.

Verify that the AccountGroupName column in both sources has the same case sensitivity. If one source has uppercase letters and the other has lowercase letters, the lookup condition may not match the data correctly. You can use the UPPER or LOWER function to convert the case of the data.

Check for any null values in the AccountGroupName column in either source. If there are, the lookup condition may not match the data correctly. You can use the ISNULL function to replace any null values with a default value.

Use data preview to inspect the data at different stages of your ETL process to see if the values in Source A and Source B are as expected and if the lookup transformation is working correctly.

I hope this helps.
Haider, Mustafa (Reigate) 25 Reputation points

2023-10-12T10:15:06.4733333+00:00

Trying above suggestions will update as a answer after doing complete testing in couple of days
Bhargava-MSFT 31,246 Reputation points Microsoft Employee

2023-10-12T16:20:28.24+00:00

Sure. Thank you, Haider, Mustafa (Reigate)

Answer 1

Hello Haider, Mustafa (Reigate),

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue: Getting Null in lookup transformation in the data flow

Source A contains AccountGroupName

Source B contains AccountGroupName and ID

Using lookup transformation and AccountGroupName = AccountGroupName to get the ID to insert into a destination.

AccountGroupName = ABC exists in both Source A and Source B

How to get the ID in the data flow to insert to destination.

Solution:

Use trim and upper functions in both source and lookup values

Expression: trim(upper("UsrName"))

If I missed anything, please let me know, and I'd be happy to add it to my answer. Feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Answer 2

Haider, Mustafa (Reigate) 25

trim(upper("UsrName")) 
Used both trim and upper function in both source and lookup values. it is working now. thanks

Share via

Getting Null in lookup transformation data flow

1 additional answer

Your answer