Forum Discussion
Replacing values with Iterations
- 1 year ago
let RULE_1 = #table({"Column1", "Column2"}, {{"a b c", "c b a"}, {"c a b", "a c b"}}), ReplacementTable = #table({"old", "new"}, {{"a", "1"}, {"b", "2"}, {"c", "3"}}), replacements = List.Buffer(Table.ToRecords(ReplacementTable)), using_table_replace_value = Table.ReplaceValue( RULE_1, null, null, (v, o, n) => List.Accumulate(replacements, v, (s, c) => Text.Replace(s, c[old], c[new])), {"Column1", "Column2"} ) in using_table_replace_value - 1 year ago
Many things are wrong in your code starting with "each..." in 4th argument of Table.ReplaceValue. I would recommend you to read something about Table.ReplaceValue (this article by Rick de Groot is pretty good).
Table.ReplaceValue is flexible but not the easiest one to understand (and not very performant at the same time) if you want to use it's full potential.
Apparently your goal is not to investigate Table.ReplaceValue but solve your particular problem. So why don't you simply show your data, explain your problem? Then maybe Table.TransformColumns would become a better choice.
I am trying to fix your code but can't do much w/o sample of your data and problem description. Here I am giving up, sorry.
( Input_Table as table, ReplacementTable as table, InputColumnToChange1 as text, //Port type 1-2 InputColumnToChange2 as text, // Body Material InputColumnToChange3 as text // Stuffing Box Material ) => let Replacements = List.Buffer(Table.ToRecords(ReplacementTable)), NewTable = Table.ReplaceValue( Input_Table, null, (x) => Record.Field(x, InputColumnToChange1) <> "Butt Welding ASME BPE", (v, o, n) => if n then List.Accumulate(Replacements, v, (s, c) => Text.Replace(s, c[Old_Material], c[New_Material])) else v, {InputColumnToChange2, InputColumnToChange3} ) in NewTable - 1 year ago
okay, i see that now. So... using List.Accumulate to go over replacements table was bad idea. I'd prefer to create a record from replacements table and check it's fields using Record.FieldOrDefault to find your values and replace them. It's faster. Anyway, below are 3 options to achieve the same result.
1. Table.ReplaceValue
(data, replacements, condition_column, condition_value, columns_list as list) => [ // generates a record with replacements repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))), // replace values in list of columns result = Table.ReplaceValue( data, (x) => Record.Field(x, condition_column) <> condition_value, // "old" null, // "new" (value, old, new) => if old then Record.FieldOrDefault(repl, value, value) else value, columns_list ) ][result]2. Table.TransformRows
(data, replacements, condition_column, condition_value, columns_list as list) => [ // generates a record with replacements repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))), transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}), // replace values in rows replace = Table.TransformRows( data, (x) => if Record.Field(x, condition_column) = condition_value then Record.TransformFields(x, transformations) else x ), result = Table.FromRecords(replace) ][result]3. Table.TransformColumns
(data, replacements, condition_column, condition_value, columns_list as list) => [ // generates a record with replacements repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))), transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}), // replace values in list of columns result = Table.SelectRows(data, (x) => Record.Field(x, condition_column) = condition_value) & Table.TransformColumns( Table.SelectRows(data, (x) => Record.Field(x, condition_column) <> condition_value), transformations ) ][result]and file
Hi Mic1979,
Table.ReplaceValue processes one value at a time for a specific column and does not support replacing values across multiple columns simultaneously using each Column1, Column2.
To handle replacements in multiple columns, use Table.TransformColumns. This function allows you to apply transformations to multiple columns within a single function call, enabling individual replacement logic for each column.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Regards,
Vinay Pabbu
This is interesting, however I would appreciate if you could provide some code example to implement the function in my case, base on what I posted before. I am not really familiar with Power Query M-language.
Thanks.
- Anonymous1 year agoNot applicable
Hi Mic1979,
Based on my R&D, here is the query from your logic.
NEW_TABLE = Table.TransformColumns(
RULE_1,
{
{"Body_Material", each List.Accumulate(
Table.ToRecords(ReplacementTable),
_, // Current value for each row in Body_Material
(valueToReplace, replaceOldNewRecord) =>
Text.Replace(
valueToReplace,
replaceOldNewRecord[Old_Material], // Old value to replace
replaceOldNewRecord[New_Material] // New value to use
)
)},
{"Other_Column", each List.Accumulate(
Table.ToRecords(ReplacementTable),
_, // Current value for each row in Other_Column
(valueToReplace, replaceOldNewRecord) =>
Text.Replace(
valueToReplace,
replaceOldNewRecord[Old_Material], // Old value to replace
replaceOldNewRecord[New_Material] // New value to use
)
)}
}
)Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Regards,
Vinay Pabbu- Mic19791 year agoPost Partisan
Hello,
thanks for this solution, however I got the following error message "We cannot convert the value "Body_Material" to type List.".
It seems very common when you use Table.TransformColumns function, however on the web I was not able to find a solution.
Thanks.