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
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
NewTableHello,
concerning the part of the code you posted:
(v, o, n) => if n then List.Accumulate(Replacements, v, (s, c) => Text.Replace(s, c[Old_Material], c[New_Material])) else v,
I am interested in understanding more about this:
- How variable v,o,n are defined?
- How did you determine which of them you will have after the if? You have n. could it be also v or o?
- Where could I find resources to better understand this structures?
I would have more questions, but I think I need to start from this basic ones.
Thanks a lot.
- AlienSx1 year agoSuper User
#1 & 2:
v (value): receives a value in a column(-s) as defined by 5th agrument.
o (old): as defined by 2nd argument.
n (new): as defined by 3rd argument.
4th argument of Table.ReplaceValue must be a function of 3 arguments. That's by design. That's how replacers (Replacer.ReplaceValue or Replacer.ReplaceText) are designed. They have 3 arguments: value, old and new. And this is how your custom replacer must be designed because Table.ReplaceValue will pass these values into your function in the same order: value, old, new.
2nd and 3rd arguments are also customized: you may use a function of single argument and Table.ReplaceValue will pass a record to it - basically a current row in the form of record. E.g. if I define my 2nd argument as (x) => x[Column10] then a value of Column10 (of the current row) will go to my replacer as it's 2nd - "old" - argument (value, old, new).
It does not matter if you choose to use 2nd or 3rd argument to calculate "true/false" in your case. I've chosen 3rd so that I use "n". It could be "o" if I'd have chosen 2nd argument. But it can't be "v" (!!!) because "v" always receives a value in columns we defined in 5th argument.
Read Rick de Groot's article - gave you a link to his site with articles about almost any object in M.
The Definitive Guide to Power Query (M) is also a good start.
Don't forget about M language specification (MS website).
- Mic19791 year agoPost Partisan
Hello
I need to understand better.
In the meatime, many thanks for your help.
- AlienSx1 year agoSuper User
as a final word: try to stop using each (which is a replacement for (_) =>) in your code. Replace it with (x) => , (y) => or whatever names you like. Then you get better control of the context you are working with.
Many authors including Rick de Groot as well as MS specification use each a lot like it's an golden rule in M language. It's not. To me it's so misleading.
- Mic19791 year agoPost Partisan
Did you read the book "The Definitive Guide to Power Query (M)"?
Is it a guide clear and simple enough for me that is a beginner?
Thanks
- AlienSx1 year agoSuper User
This book is simple enough. Go to amazon, read some preview pages and make a decision. This book is not about mouse clicking. That's all I can say. This book won't probably reveal all of Table.ReplaceValue secrets though 😀
- Mic19791 year agoPost Partisan
Hello
sorry but I did not understand the following point:
- 2nd and 3rd arguments are also customized: you may use a function of single argument and Table.ReplaceValue will pass a record to it - basically a current row in the form of record
together with
-n (new): as defined by 3rd argument
Don't we need to get the "new" value from the "Replacements" record?
Thanks
- AlienSx1 year agoSuper User
Please provide a sample of your data and replacements table as well as expected result.