What is generally faster - Table.ReplaceValue or Table.TransformColumns, for multiple replacements?

[andreyminakov]

Hi All!
I’ve got a question - if I need to do several replacements in a table column - what is the fastest way to do that, by design (does NOT matter that one way allows to make more types of transofmations)?

[v-yuezhe-msft]

@andreyminakov,

It depends on your data. How would you replace the values in the column? To optimize multiple replacements. you can combine Table.ReplaceValue and Table.TransformColumns functions.

There is a similar thread for your reference:
https://community.powerbi.com/t5/Desktop/Optimizing-multiple-replacements/td-p/102389

Regards,
Lydia

[andreyminakov]

Hi Lydia! Thanks a lot for your replay and useful thread regarding the task of replacement of several chars in a table. But I still would like to understand - what is better way to solve the task from time of execution point of view, and haven't found info about that there.

In general, I can do the task of remooving (as simpler variant of replacement) of a list of characters in all the columns of a table in these ways:

1. Multiple calls of Table.ReplaceValue (it's possible to make these call through List.Generate, but it doesn't influence the time of execution, I guess).

Table.ReplaceValue(Source,".","",Replacer.ReplaceText,{Table.ColumnNames(Source)})
Table.ReplaceValue(Source,",","",Replacer.ReplaceText,{Table.ColumnNames(Source)})
...

2. Call of Table.TransformColumns

Table.TransformColumns(
	Source, 
	List.Zip({
		Table.ColumnNames(Source), 
		List.Repeat(
			{each Text.Remove(_, Text.ToList(".:;?!<>@#$%^&*=+"))},
			Table.ColumnCount(Source))
		})
	)

3. Call Custom Function in Table.ReplaceValue

Table.ReplaceValue(
	Source,
	".:;?!<>@#$%^&*=+",
	"",
	(x,y,z) => List.Accumulate(
		Text.ToList(y), 
		x, 
		(s, c) => Text.Remove(s, c)
		),
	Table.ColumnNames(Source)
	)

And the question is - what is quicker by design of PQ?

What creates more overhead:

1. doing all the transformations at once for each row, but spending time PER ROW on preparing data needes for removing (making list from the text ".:;?!<>@#$%^&*=+", in the example above),

2. or prepare the data once (separating the text ".:;?!<>@#$%^&*=+" on chars before), and then go several times through all the rows with every particular char?

[v-yuezhe-msft · Accepted solution]

@andreyminakov,

You can analyze the power query performance by using trace log. See https://www.excelando.co.il/en/analyzing-power-query-performance-source-large-files/.
 



Regards,
Lydia