Creating a column which returns the value based on the condition on a column from different table

Hi everyone,

I would like to create a DAX formula that creates a new column in "Data View", or in Power Query using M. The example I provide in the below has 3 distinct values but, the exact table I'm working with has 508 distinct values, so using a conditional column that has more than 500 if clauses are quite problematic.

Therefore, I need your help to create the column based on the below logic using DAX or M language if it is applicable.

I have two tables, X and Y. Table X has values like (including null values in the second column, and this is the main reason why I need to create a new custom column which I can use instead of this non-completed column):

Table Y has only distinct values and no null cells.

What I would like to do is creating a new custom column using DAX or M which retrieves the names based on the condition between the ID column in the table X and table Y.

Looking forward to having your support.

Thanks in advance.