Match 2 columns from different columns

[Jeffreyjar]

Hello, 

I'm kind of new in power bi so im doing small projects to perform myself,

 im doing a ranking of each agents of a company and each agent has his own code and it is changing every time  

I have an issue on matching 2 columns in different tables 

Need help asap

the old column is "Numero Agents" of the table "APE 02 2022" base on their name

Numero Agents	Noms Agents
5599000	DIRECT BUSINESS
5599000	DIRECT BUSINESS
5599000	DIRECT BUSINESS
5599000	DIRECT BUSINESS
A10299002	MENDOUME ERIC VALENTIN
94202301	EXPRESS INSURANCE
B50199001	YONDO MIYENGA ALICE
A10199018	LEHMAN CHARLES FREDERIC
A10199012	JIGHEULA WELAPINOU ELIONOR
A10101001	NEMY OMOG SERGE FRANCOIS
A10199015	NGUEKENG JOLIVETTE LUCIE
A10199012	JIGHEULA WELAPINOU ELIONOR
A10199012	JIGHEULA WELAPINOU ELIONOR
A10199012	JIGHEULA WELAPINOU ELIONOR
A10199006	NGONKOLO MARIE MADELEINE
A10199006	NGONKOLO MARIE MADELEINE
A10199012	JIGHEULA WELAPINOU ELIONOR
A10199006	NGONKOLO MARIE MADELEINE
A10101001	NEMY OMOG SERGE FRANCOIS
A10199001	MBA NEE TENE LEOPOLDINE
A10199015	NGUEKENG JOLIVETTE LUCIE

with this new column  "New Agents Codes" from the table "agent list" base on their name

New Agents Codes	EAAGTNAME
A10199003	RHODE DORETTE MOSSI EKAME
A10199006	NGONKOLO MARIE MADELEINE
A10199007	HONTCHEU MONKAM DOMINIQUE
A10199010	BEKONO KOMBO JEANNINE CHARLENE
A10199012	JIGHEULA WELAPINOU ELIONOR
A10199013	SAFEDIEU EDITH VADES
A10199015	NGUEKENG JOLIVETTE LUCIE
A10199017	NDO FOUDA BERNADETTE CHRISTALE
A10199018	LEHMAN CHARLES FREDERIC
A10299002	MENDOUME ERIC VALENTIN
A10299004	MENI CAROLLE NELLY
A10299005	MATHA DORIANNE CLAIRE
A10299006	BELLA AWONO ANTOINE ALFRED
A10299007	BIENDU KOUAGHU MIKEL FLORE
A10299008	TCHANA KUIGOUA EDWIGE
A10299010	MANDONG CHRYSTELLE NICOLE
A10299011	DJUIDJE FOGAING MARIE CHRISTEL
A10299012	MBOM LOTIN BERTHY AURELIE
A10299013	NYANDJOU TCHOUMGA PATRICK B.
A10399002	TCHAOU STEPHANE
A10399003	TCHAMBA NGAHA DONALD
A10399004	TCHEUFANG FALONNE
A10399006	MANET CHRISTINE PERSIDE GRACE
A10399015	CHEBOU CHEKEM ELIANE JOCELYN
A10399016	IKOU WELNJEL DANIEL HERVE
A10399018	AWOUMOU NGA OWONA VANESSA

[hokeson · Accepted solution]

First step you need to do is to deduplicate records in table APE 02 2022.

then you can merge both table based on name to get all combinations.

it depends of usecase. 

please see M-code for deduplication

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZHBasMwEER/ZfE5B9mtS3NU7KmzibQbJCutCfn/3+jKlEJ6KDQ0CHTZtzPD7OXS9P1265xrNs3ICcNMu5JZkHNz3Txy6lvX1Xln8wgZtUQQEg909gEys6zY9rlz3ZNrjcLHKdk2seSSvAxYgV3v2qpTiUVNhyIvkMmTD/zFmFdl2ldjAvbRCw17nwIyvSWM1fWGq5kOPO1Rgqd3BH9i0UIIrKLpG7W32griQhp1oow0wTQtnXK+0ewrOBUcLRsdNPAZ8wwKZWDcY/4w1L2sSVWOGqxMnxj2jwiw490F/rv5X4pfwbjzJADNEKscetIw/gz5+4Gunw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Numero Agents " = _t, #"Noms Agents" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numero Agents ", type text}, {"Noms Agents", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Numero Agents "})
in
    #"Removed Duplicates"

see M-code for merge

let
    Source = Table.NestedJoin(#"New Agents codes", {"EAAGTNAME"}, #"APE 02 2022", {"Noms Agents"}, "APE 02 2022", JoinKind.FullOuter),
    #"Expanded APE 02 2022" = Table.ExpandTableColumn(Source, "APE 02 2022", {"Numero Agents ", "Noms Agents"}, {"Numero Agents ", "Noms Agents"})
in
    #"Expanded APE 02 2022"

result looks like 

[hokeson]

Hello,

you can use a formula comparing values and returning requested result. Do you have some sample data and expected result?