How to match 2 columns from 2 differents tables to have 01 single column updated

[Jeffreyjar]

Hello

i  have an issue on matching 2 columns in different tables. One is up to date, the another one is old

Need help asap @amitchandak @Jihwan_Kim @tamerj1 @johnt75 

the new column (calculated with dax) is "New Agents Codes" of the table "agent list"

Table: agent list

New Agents Codes	EAAGTNAME
A79901001	ATSOL NDONGO NATHALIA
A19999062	LIENOU GAYAP GHISLAINE
A39908001	DITONE ARNAUD THEOPHILE
B49903001	KENFACK DONFACK WILLIAM JOSPIN
A19999063	ROMAY-BELL EDINGA ESSENGUE D

the old column is "Numero Agents" of the table "APE February"

Numero Agents	Noms Agents
93003408	KENFACK DONFACK WILLIAM JOSPIN
32699002	ATSOL NDONGO NATHALIA
32599157	ROMAY-BELL EDINGA ESSENGUE D
32599171	LIENOU GAYAP GHISLAINE
32402304	DITONE ARNAUD THEOPHILE
26499045	FOTSO MEMO EPSE FOTSO C.
26499008	MOYUE FRANCOISE LADOUCE

the expected output  is 

Table: APE february

Numero Agents	Noms Agents
B49903001	KENFACK DONFACK WILLIAM JOSPIN
A79901001	ATSOL NDONGO NATHALIA
A19999063	ROMAY-BELL EDINGA ESSENGUE D
A19999062	LIENOU GAYAP GHISLAINE
A39908001	DITONE ARNAUD THEOPHILE

kind regards

[tamerj1 · Accepted solution]

Hi @Jeffreyjar 
Here you go 

New Code = RELATED ('agent list'[New Agents Codes] )

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New table = 
FILTER (
    SELECTCOLUMNS (
        'Agent list',
        "Numero Agents", 'Agent list'[New Agents Codes],
        "Noms Agents", 'Agent list'[EAAGTNAME]
    ),
    [Noms Agents] IN VALUES ( 'APE february'[Noms Agents] )
)

[Jeffreyjar]

scalar problem @Jihwan_Kim 

[Jeffreyjar]

can you do an example please?

@tamerj1 

[tamerj1]

@Jeffreyjar 

Easiest to create a relationship between the agent name columns in both tables then use RELATED to retrieve the the new agent code. Otherwise you can FILTER Agents List with CONTAINSSTRING and wrap it all with MAXX to retrieve the new codes column