Forum Discussion
Replacing string values in a query column with values from another query
For your reference.
I'm not sure if this method will shorten the processing time.
Step 0: I use these data below.
<MOE>
<Product>
Step 1: I duplicate 'Expression' column.
Step 2: I split 'Expression - Copy' column by space.
<After>
Step 3: I unpivot 'Expression -Copy.1-' - 'Expression -Copy.10-' column.
Step 4: I merge queries and expand product..
<After>
Step 5: I remove 'Expression' column and 'Value' column.
Step 6: I pivot column.
<After>
Step 7: I reorder columns.
Step 8: I merge columns with space.
<After>
Hello sbcict
Your code works correctly row-count-wise, but it's slow because you're doing 100k passes of Table.ReplaceValue over 5000 rows (~500M string scans). It also risks false matches - e.g. "A1" getting replaced inside "A12".
Fix: tokenize each expression, look up each token in a Record (O(1) hash lookup), join back.
let
Source = #"Renamed Columns",
Lookup = Record.FromTable(
Table.RenameColumns(
Table.SelectColumns(Product, {"Number", "Title"}),
{{"Number", "Name"}, {"Title", "Value"}}
)
),
Keywords = {"OR", "AND", "NOT", "(", ")"},
ReplaceTokens = (expr as text) as text =>
Text.Combine(
List.Transform(
Text.Split(expr, " "),
each if List.Contains(Keywords, _) then _
else Record.FieldOrDefault(Lookup, _, _)
),
" "
),
#"Replaced Value" = Table.TransformColumns(
Source, {{"Expression", ReplaceTokens, type text}}
)
in
#"Replaced Value"If parentheses are stuck to tokens ("(A"), pad them first: Text.Replace(expr, "(", "( ") etc., then strip the padding at the end.
Cheers,
Metrica Team.
thank you mickey64!! your suggestion was within my capabilities and i got the results that i expected!!
