Forum Discussion
How to replace text using a lookup table without duplicating text
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
3 Replies
Thank you, this has worked perfectly! Really appreciate your help.
#"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"
