Forum Discussion
Replacing string values in a query column with values from another query
i have a column, titled "Expression", in a query table named "MOE" where each column row has a string of values separated by spaces, e.g. "A OR B OR C OR D AND NOT E", about 5000 rows long.
i have another query table, titled "Product", that has 2 columns, one titled "Number" and the other column titled "Title", and this table has over 100 thousand rows.
i want to replace the "Number" values with their respective "Title" values in MOE's Expression column, e.g. Bench OR Table OR Chair OR Stool AND NOT Rocker.
i've looked and looked for examples of code and have used List.Accumulate to some degree of success as i can watch it correctly replace values but it runs forever because its trying to build a 5000 x 100,000 row table. I stopped it when i saw it reach a million rows.
here is the code i was using:
#"Replaced Value" = List.Accumulate(
Table.ToRecords(Product),
#"Renamed Columns",
(state, current) => Table.ReplaceValue(
state,
current[Number],
current[Title],
Replacer.ReplaceText,
{"Expression"}
)
)
in
#"Replaced Value"
what's the code that i need to use that will replace the values but keep the original number of rows in the MOE table?
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>
7 Replies
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>
thank you!!!!!! your suggest worked and was within my capabilities1
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!!
Hi,
Does this M code code work any better?
let Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Amount", type number}, {"ID Category", type text}}), Custom1 = Table.ToRows(Table29), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.ReplaceMatchingItems(Text.Split([ID Category],","),Custom1),", ")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID Category"}) in #"Removed Columns"Table29 has 2 columns. ID and Product Category. Table28 has 2 columns Sales Amount and ID Category. Under the ID Category column are comma seperated numbers in each cell. So the code above iterated over every cell of the ID Category column and replaces every comma seperated number wti the Category availale in Table29.
Hope this helps.
Can you please share your demo input and expected output!
Hi sbcict,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Ahmedx, mickey64, Ashish_Mathur, metrica, for those inputs on this thread.Has your issue been resolved? Could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
I hope this information is helpful. If you have any questions about the solutions, please feel free to contact us. We are happy to assist you.
Thank you.
