Replace Text using a Lookup Table

[WALEED]

Gents,

It's hard to explain my problem using words so I'll list a few examples here:

I have 2 tables:

1 holding the data:

TAG	DATA SHEET	BLOCK DIAGRAM
BOB101	AAA;BBB,CCC	AAA;BBB,CCC
BOB102	AAA;BBB,CCC	AAA;BBB,CCC
BOB103	AAA;BBB,CCC	AAA;BBB,CCC

1 holding the rules for text replacement

Attribute	Old Text	New Text
DATA SHEET	;	|
DATA SHEET	,	|
BLOCK DIAGRAM	;	|
BLOCK DIAGRAM	,	|

I'm after the results below:

TAG	DATA SHEET	BLOCK DIAGRAM
BOB101	AAA|BBB|CCC	AAA|BBB|CCC
BOB102	AAA|BBB|CCC	AAA|BBB|CCC
BOB103	AAA|BBB|CCC	AAA|BBB|CCC

The dataset is huge and so is the set of rules, they're on 2 separate excel files.

I'm after a dynamic approach where I can simply add lines to the rules file and see the changes after a refresh.

[vanessafvg]

@WALEED why dont you just do a replace in powerquery (M)?

right click on the column in the query editor and select replace values?

[WALEED]

That would be ideal for a simple dataset.

My problem is that the list of rules is ever changing, adding/removing as new problems arise.

And the number of columns I have is 243 (can grow wider in the future)

The rules excel sheet is stored in a shared area where the team can add rows without opening the super heavy PBI file.

[vanessafvg]

@WALEED sounds like you need to create a function then and pass parameters?

https://blog.learningtree.com/creating-functions-m-power-query-formula-language/

https://www.mattmasson.com/2014/11/converting-a-query-to-a-function-in-power-query/

[WALEED]

If only I could cook up the code to feed the function 😄 please help!

[vanessafvg]

@WALEED email me ill give it a bash vanessafvg@gmail.com

[WALEED]

@vanessafvg

1. I've unpivoted the data table. All column names are now under "Attribute", and all values are under "Value"

2. I think I'm close. The formula is valid but the results are unchanged:

Custom1 = 
Table.ReplaceValue(LastStep,
each if [Attribute] = RULE_TABLE[Attribute] then RULE_TABLE[OLD VAL] else RULE_TABLE[NEW VAL],
each RULE_TABLE[NEW VAL],
Replacer.ReplaceText,
{"Value"})

Maybe I'm misplacing the "each"?

[WALEED · Accepted solution]

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

ATTR	OLD VAL	NEW VAL
DATA SHEET	;	|
DATA SHEET	,	|
BLOCK DIAGRAM	;	|
BLOCK DIAGRAM	,	|

DATA_TABLE

TAG	DATA SHEET	BLOCK DIAGRAM
BOB101	AAA;BBB,CCC	AAA;BBB,CCC
BOB102	AAA;BBB,CCC	AAA;BBB,CCC
BOB103	AAA;BBB,CCC	AAA;BBB,CCC

DATA_TABLE(UNPIVOT)

TAG	InputAttr	InputValue
BOB101	DATA SHEET	AAA;BBB,CCC
BOB101	BLOCK DIAGRAM	AAA;BBB,CCC
BOB102	DATA SHEET	AAA;BBB,CCC
BOB102	BLOCK DIAGRAM	AAA;BBB,CCC
BOB103	DATA SHEET	AAA;BBB,CCC
BOB103	BLOCK DIAGRAM	AAA;BBB,CCC

DATA_TABLE(Add Column with Function Above)

TAG	InputAttr	InputValue	Changed Text
BOB101	DATA SHEET	AAA;BBB,CCC	AAA|BBB|CCC
BOB101	BLOCK DIAGRAM	AAA;BBB,CCC	AAA|BBB|CCC
BOB102	DATA SHEET	AAA;BBB,CCC	AAA|BBB|CCC
BOB102	BLOCK DIAGRAM	AAA;BBB,CCC	AAA|BBB|CCC
BOB103	DATA SHEET	AAA;BBB,CCC	AAA|BBB|CCC
BOB103	BLOCK DIAGRAM	AAA;BBB,CCC	AAA|BBB|CCC

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