Forum Discussion

andreyminakov's avatar
andreyminakov
Frequent Visitor
7 years ago
Solved

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

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 transof...
General Comment
Tips and Tricks
andreyminakov's avatar
andreyminakov
Frequent Visitor
7 years ago
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?