How to replace text using a lookup table without duplicating text

[JoMont]

I have the following table

Medical Registration Number	Discipline Type	Facility Type	Start Date	End Date	Discipline Group
MED1043	Primary Health ACCHS	Primary Health	4/05/2023	21/06/2023	Community ACCHS
MED1047	Primary Health	Several Primary Health clinics	2/11/2023	4/02/2024	Community
MED1048	Obstetrics and Gynaecology	Hospital	8/01/2023	21/01/2023	Obstetrics and Gynaecology and Obstetrics and Gynaecology and Gynaecology
MED1052	Obstetrics	Hospital	21/08/2023	29/10/2023	Obstetrics and Gynaecology
MED1054	Primary Health	Several Primary Health clinics	5/02/2024	1/05/2024	Community

The Discipline Group column is generated from a lookup table with the following entries (among others)

Detailed Discipline	Discipline Category
Primary Health	Community
Primary Health ACCHS	Community
Rural GP	Community
Gynaecology	Obstetrics and Gynaecology
Gynaecology (only)	Obstetrics and Gynaecology
Obstetrics & Gynaecology	Obstetrics and Gynaecology
Obstetrics (only)	Obstetrics and Gynaecology
Obstetrics and Gynaecology	Obstetrics and Gynaecology

I have the following code that adds the Discipline Group column, based on the value in the Discipline Type column:

#"Add Discipline Group" = Table.AddColumn(#"Removed Columns", "Discipline Group", each List.Accumulate(
Table.ToRecords(Discipline_Type_Lookup), [Discipline Type],( valueToReplace, replaceOldNewRecord ) => Text.Replace( valueToReplace, replaceOldNewRecord[Detailed Discipline], replaceOldNewRecord[Discipline Category])
))
in
#"Add Discipline Group"

How do I get an exact match, so that "Obstetrics and Gynaecology" in the Discipline Type column does not become "Obstetrics and Gynaecology and Obstetrics and Gynaecology and Gynaecology" in the Discipline Group column, and is instead just "Obstetrics and Gynaecology"? This is also happening with "Primary Health" and many more entries in my list ("Surgery", "General Surgery" etc).

There are currently 141 entries in the Detailed Discipline column, so I definitely need a list function rather than manual replacements.

Thanks

[wdx223_Daniel]

#"Add Discipline Group" = let a=List.Buffer(Table.ToRows(Table.Group(Discipline_Type_Lookup,"Discipline Category",{"n",each [Detailed Discipline]}))) in Table.AddColumn(#"Removed Columns", "Discipline Group", each List.Select(a,(x)=> List.Contains(x{1},[Discipline Type])){0}?{0}?)
in
#"Add Discipline Group"

[JoMont]

Thank you, this has worked perfectly! Really appreciate your help.

[SundarRaj · Accepted solution]

Hi @JoMont ,
You can use the List.PositionOf function which shall give you the exact match according to the LookUps. I'll attach the imageşof the output and the file link below. Let me know if I have understood your query correctly and in case of any doubts as well.

Thanks,