Replace character when not in a word

[Anonymous]

Hello all,

This issue has had me going for a few hours now so am turning to the smarter brains of this forum 🙂

I have a column named "discipline" that can contain up to 9 characters along a drawing reference, in the following format:

Drawing	Discipline
WG1234	A
WG 125	A, C
WG 9090	A, C, E, J, K, M, P, S, T
WG 087	E

The letterts A, C etc cannot be changed in the raw database so I have to import them as is but I'd like to convert A to Architectural, C to Civil, E to Electrical, etc... for the purpose of filtering (I'd actually like to keep the Discipline column as it is)

The intent being that I then have a slicer with the listing of Architectural, Civil, Electrical and if I select Architectural, it will show in my table all drawings that have A in the Discipline( in the example above that is WG1234, WG 125 and WG 9090)

I have obviously tried to replace values sequentially in power query mode but I end-up with gibberish (A becomes ARCHITECTURAL, but then if E becomes ELECTRICAL I end-up with ARCHITELECTRICALCTURAL...). I have thought of unpivoting the data but I already have a couple million rows and it wouldn't be sustainable.

Any clever solution you can suggest?

Thanks in advance.

OF