cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WALEED Regular Visitor
Regular Visitor

Replace Text using a Lookup Table

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:

TAGDATA SHEETBLOCK DIAGRAM
BOB101AAA;BBB,CCCAAA;BBB,CCC
BOB102AAA;BBB,CCCAAA;BBB,CCC
BOB103AAA;BBB,CCCAAA;BBB,CCC

 

1 holding the rules for text replacement

AttributeOld TextNew Text
DATA SHEET;|
DATA SHEET,|
BLOCK DIAGRAM;|
BLOCK DIAGRAM,|

 

I'm after the results below:

TAGDATA SHEETBLOCK DIAGRAM
BOB101AAA|BBB|CCCAAA|BBB|CCC
BOB102AAA|BBB|CCCAAA|BBB|CCC
BOB103AAA|BBB|CCCAAA|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.

1 ACCEPTED SOLUTION

Accepted Solutions
WALEED Regular Visitor
Regular Visitor

Re: 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

ATTROLD VALNEW VAL
DATA SHEET;|
DATA SHEET,|
BLOCK DIAGRAM;|
BLOCK DIAGRAM,|

 

DATA_TABLE

TAGDATA SHEETBLOCK DIAGRAM
BOB101AAA;BBB,CCCAAA;BBB,CCC
BOB102AAA;BBB,CCCAAA;BBB,CCC
BOB103AAA;BBB,CCCAAA;BBB,CCC

 

DATA_TABLE(UNPIVOT)

TAGInputAttrInputValue
BOB101DATA SHEETAAA;BBB,CCC
BOB101BLOCK DIAGRAMAAA;BBB,CCC
BOB102DATA SHEETAAA;BBB,CCC
BOB102BLOCK DIAGRAMAAA;BBB,CCC
BOB103DATA SHEETAAA;BBB,CCC
BOB103BLOCK DIAGRAMAAA;BBB,CCC

 

DATA_TABLE(Add Column with Function Above)

TAGInputAttrInputValueChanged Text
BOB101DATA SHEETAAA;BBB,CCCAAA|BBB|CCC
BOB101BLOCK DIAGRAMAAA;BBB,CCCAAA|BBB|CCC
BOB102DATA SHEETAAA;BBB,CCCAAA|BBB|CCC
BOB102BLOCK DIAGRAMAAA;BBB,CCCAAA|BBB|CCC
BOB103DATA SHEETAAA;BBB,CCCAAA|BBB|CCC
BOB103BLOCK DIAGRAMAAA;BBB,CCCAAA|BBB|CCC

 

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

7 REPLIES 7
Super User
Super User

Re: Replace Text using a Lookup Table

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

 

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

 

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
WALEED Regular Visitor
Regular Visitor

Re: Replace Text using a Lookup Table

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.

Super User
Super User

Re: Replace Text using a Lookup Table

@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/


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
WALEED Regular Visitor
Regular Visitor

Re: Replace Text using a Lookup Table

If only I could cook up the code to feed the function Smiley Very Happy please help!

Super User
Super User

Re: Replace Text using a Lookup Table

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
WALEED Regular Visitor
Regular Visitor

Re: Replace Text using a Lookup Table

@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 Regular Visitor
Regular Visitor

Re: 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

ATTROLD VALNEW VAL
DATA SHEET;|
DATA SHEET,|
BLOCK DIAGRAM;|
BLOCK DIAGRAM,|

 

DATA_TABLE

TAGDATA SHEETBLOCK DIAGRAM
BOB101AAA;BBB,CCCAAA;BBB,CCC
BOB102AAA;BBB,CCCAAA;BBB,CCC
BOB103AAA;BBB,CCCAAA;BBB,CCC

 

DATA_TABLE(UNPIVOT)

TAGInputAttrInputValue
BOB101DATA SHEETAAA;BBB,CCC
BOB101BLOCK DIAGRAMAAA;BBB,CCC
BOB102DATA SHEETAAA;BBB,CCC
BOB102BLOCK DIAGRAMAAA;BBB,CCC
BOB103DATA SHEETAAA;BBB,CCC
BOB103BLOCK DIAGRAMAAA;BBB,CCC

 

DATA_TABLE(Add Column with Function Above)

TAGInputAttrInputValueChanged Text
BOB101DATA SHEETAAA;BBB,CCCAAA|BBB|CCC
BOB101BLOCK DIAGRAMAAA;BBB,CCCAAA|BBB|CCC
BOB102DATA SHEETAAA;BBB,CCCAAA|BBB|CCC
BOB102BLOCK DIAGRAMAAA;BBB,CCCAAA|BBB|CCC
BOB103DATA SHEETAAA;BBB,CCCAAA|BBB|CCC
BOB103BLOCK DIAGRAMAAA;BBB,CCCAAA|BBB|CCC

 

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