Replace Text using a Lookup Table

Changed the whole approach to a function. Many thanks vanessafvg

I had hoped that a merge would do the trick. It works perfectly for a full replacement of a value but this one's trickier.

(InputAttr,InputValue)=> 
let
	SubTable = Table.SelectRows(REPLACE_TABLE,each [ATTR] = InputAttr),
	DoReplacement = List.Generate(
		  ()=> [Counter=0, MyAttr=InputAttr, MyText=InputValue], 
		  each [Counter]<=List.Count(SubTable[OLD VAL]), 
		  each [Counter=[Counter]+1,
				MyText=Text.Replace(
						 [MyText], 
						 SubTable[OLD VAL]{[Counter]}, 
						 SubTable[NEW VAL]{[Counter]})], 
		  each [MyText]),
	GetLastValue = List.Last(DoReplacement)
in
	GetLastValue

Used this function to add a column; using the GetLastValue of the function as a Value for each row.

InputAttr is the Attribute/Field Name (after pivoting the table).

InputValue is the text that needs to be partially replaced.

List.Generate iterates through the text changes (in order of the RULES_TABLE).

Days of trial and error. not sure if I fully understand what I did there. Steps below:

REPLACE_TABLE

DATA_TABLE

DATA_TABLE(UNPIVOT)

DATA_TABLE(Add Column with Function Above)

Straightforward from there; delete old column and re-pivot using unique TAG and InputAttr.