Replacing values with Iterations

[Mic1979]

Dear all,

I am always dealing with the topic to replace values and solve an issue I have in my daily job.

I found a very useful blog, and I successfully used this function:

NEW_TABLE = Table.ReplaceValue (
RULE_1,
each [Body_Material],
each List.Accumulate(
Table.ToRecords(ReplacementTable),
[Body_Material],
(valueToReplace, replaceOldNewRecord) =>
Text.Replace (
valueToReplace,
replaceOldNewRecord[Old_Material],
replaceOldNewRecord[New_Material]
)
),
Replacer.ReplaceText,
{"Body_Material"}
)

In this case, I am replacing the values in one column. How to do this is two columns at the same step?

I could apply the same adding a step and changing the column where tu apply the function, however I think this will be more time consuming operation that have everything in the same step.

[AlienSx · Accepted solution]

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

[AlienSx · Accepted solution]

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

[watkinnc]

I don't know why you all have to get all fancy, when plain old Table.Join to your replacement table would work just fine. Even two separate join steps would probably be more efficient if you need to replace values in two columns.

You know, you could just do

= Table.AddColumn(PriorStepOrTableName, "Replaced", each List.ReplaceMatchingItems(List.Skip(Record.ToList(_)), List.Zip({ReplacementTable[Column1], ReplacementTable[Column2]})))

Then just remove your old columns, expand the list values to columns, and that's it.

--Nate

[Mic1979]

Thanks for your input. I will try both solutions in the next hours.

Really Appreciated.

[AlienSx · Accepted solution]

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

[Mic1979]

Hello

I was trying to add a condition now:

(
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,
null,
each if [InputColumnToChange1] <> "Butt Welding ASME BPE" then (v,n,o) => List.Accumulate (
Replacements,
v, (s,c) => Text.Replace (
s, c[Old_Material], c[New_Material]))
else _,
{"Body_Material", "Stuffing_Box_Material"}
)

in
NewTable

but this is not working.

Did I put the "if" in the wrong place?

Thanks again.

[AlienSx · Accepted solution]

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

[Mic1979]

Hello,

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:

1. How variable v,o,n are defined?
2. How did you determine which of them you will have after the if? You have n. could it be also v or o?
3. 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.

[AlienSx]

#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).

[Mic1979]

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

[AlienSx]

Please provide a sample of your data and replacements table as well as expected result. 

[Mic1979]

Here to you the link. I do hope I made everything correctly and you are able to view it:

https://docs.google.com/spreadsheets/d/19htHq5lGHaT9rocGiZ622dfz-cFVUzIt/edit?usp=drive_link&ouid=10...

Inside you have the sample file, the replacement table, the function we are discussing about the the result of it.

The function provides me with the correct result, I just would like to understand better your explanation, this is the reason behind my last questions.

Thanks a lot for your help.

[AlienSx]

I can't view your data - restricted access. Never mind, the answer to your question is NO. You don't need "new" value from replacements table because you run List.Accumulate that goes over your replacements table and do the job with "value" 

[Mic1979]

Hello,

if you want, I updated the rights to access.

Thanks.

[AlienSx · Accepted solution]

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

[Mic1979]

Hello AlienSx I am studying your sulution. The first you gave is pretty clear. I am struggling a little bit with your second as I did not understand this part:

transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),

Here my questions:

1. columns_list is input argument, so it is fine.

2. What is the meaning of the function (name)?

3. What are the arguments passed to this function?

4. In general I am always struggling with this type of construction:

(x) => if Record.Field(x, condition_column) = condition_value
then Record.TransformFields(x, transformations)
else x

As I did not understand what are the values that the argument x assumes.

Could you help me?

Many thanks.

[AlienSx]

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. 

[Mic1979]

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.

[Mic1979]

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 or

Record.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.

[Mic1979]

Many thanks. I really appreciate your support.

[Mic1979]

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

[AlienSx]

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 😀