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 transofmations)?
General Comment
Tips and Tricks

3 Replies

    • 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?