Merging Tables to Look-Up but Returning Null

[LW_777]

Hi There,
I am using the merge query function to look-up values in another table (its product item numbers, therefore numbers and text) - trying to return the generic model numbers to help create a unique key between tables.  I have checked the data between the tables is in the same format (text) and there are no additional spaces/characters in either of the columns of data, yet Power Query is returning null.  Just doing a quick sample of the data in Excel and Excel returns true for an exact match.

Anyone have any other hints or ideas as to how I can resolve this please? 
My BI knowldge is exceptionally basic right now and trying to force myself to use BI given the size of data involved as I would always use Excel as standard.
Thank you.

[LW_777 · Accepted solution]

Hi,

Thank you for your reply.

Both fields are upper text and numbers so it's not this causing the issue. 

In the end my manager my manager found the solution.  There was indeed spaces in one of the fields that didn't show when exporting to Excel or as text.  We performed the following steps to clean the data in order for the merge query to work:

Whilst in transform mode (Power Query),

Right click on the column to clean - select 'Replace Values'

In 'Value To Find' put a single space (therefore cannot see it).

In 'Replace With', leave this blank / nothing

Click ok.

[BA_Pete]

Hi @LW_777 ,

Have you also checked capitalisation? Power Query is entirely case-sensitive.

Try running Text.Upper on both [Item Number] and [ItemNum] before the merge.

Pete

[LW_777]

Thank you Pete, it wasn't case sensitivity as all the product IDs are upper case and numbers.
Thak you for replying though.  Appreciate your time.  My manager tried a solution that work per my reply posted.

Laura