Forum Discussion

LW_777's avatar
LW_777
New Member
3 years ago
Solved

Merging Tables to Look-Up but Returning Null

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 ...
Need Help
  • LW_777's avatar
    LW_777
    3 years ago

    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's avatar
BA_Pete
Super User
3 years ago

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's avatar
LW_777
New Member
3 years ago

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