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
2. List.Transform requires a function as 2nd argument. (name) => is that function while "name" itself is just a variable name - like x, y or _.
3. List.Transform passes column_list items, one by one.
4. Look at the context: we use this function as an argument of Table.TransformRows which in turn passes records (rows of the table) one by one to transformation function. So that x is record.
Hello AlienSx,
always concerning your second solution, I tested it and I was wondering if it could be possible to make a change as in this way:
(data, replacements, condition_column as list, condition_value as list, 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]
The reason is because I am trying to check the conditions in two columns, instead of one.
I implemented that, and I invoked it in this way:
RULE_4 = replace_value_2(
#"Removed Columns1",
ReplacementTable_Body_StuffingBox,
{"Product_Series_Description","DN_Size"},
{"3 way", "DN10"},
{"Body_Material", "Stuffing_Box_Material"})
However the following error returned:
Could you suggest a solution?
Thanks.
- Mic19791 year agoPost Partisan
I solved in this way:
(data, replacements, condition_column1, condition_column2, condition_value1,condition_value2, 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_column1) = condition_value1 orRecord.Field(x, condition_column2) = condition_value2
then Record.TransformFields(x, transformations)
else x
),
result = Table.FromRecords(replace)
][result]and IT WORKS!
However I have an issue with the original posted solution. In the condition value, I need to add a list of values to check in the column condition_column. I tried to do in this way:
RULE_2 = replace_value_UniqueCondition(
RULE_1,
ReplacementTable_Body_StuffingBox,
"Port_Type_1_2",
{"Butt Welding ASME BPE","Butt Welding DIN 11850 s2", "Butt Welding ISO 1127", "Butt Welding SMS 3008", "Clamp ASME BPE", "Clamp DIN 11850 s2", "Clamp ISO 1127", "Clamp SMS 3017", "Flange ANSI Standard 150", "Flange DIN EN 1092-1"},
{"Body_Material", "Stuffing_Box_Material"}),and this is not working, it does not give any syntax error, but the function does not do what I would like.
Any inputs?
Thanks.