Record.TransformFields error

[Mic1979]

Dear all,

I am trying to understand how to use some functions to replace value in a table with the records. This is the function I am speaking about.

• I have a record defined in the function named Record_Region with two fields, the second one named step is not important.
• I am passing argument to this function that is a replacement table, a table where I am defining how to replace the values in the first field of the record, named Region
• Record.TransformFields returns the following error message: 

This is the function:

(replacements) =>
[
// generates a record with replacements
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),

Record_Region = [ Region = { "AM.", "EMEA", "APAC" }, Step = { "A", "B", "C" } ],

B4 = Record.TransformFields (Record_Region, {"Region", (x) => Record.FieldOrDefault(repl,x)})

][B4]

Could you support?

Thanks a lot.

[Omid_Motamedise]

your problem is not about the function, you have some other mistake

In  Record.TransformFields Transformation should be in type function, so you can have to use a record in its first argument and then a list inculding the name of field and the transformation function. for example 

= Record.TransformFields( [OrderID = 1, Price = 100], {"Price", (x)=> x/10} )

in the provided example, x reperesent value 100, so if you use it inside (x) => Record.FieldOrDefault(repl,x)}), it present null always (as it is equal to  Record.FieldOrDefault(repl,200)

but the main problem is for the below notation, you cant use the name of step B4 at the end of that step, it will provide cycle

B4 = Record.TransformFields (Record_Region, {"Region", (x) => Record.FieldOrDefault(repl,x)})

][B4]

[AlienSx]

---

@Omid_Motamedise wrote:

but the main problem is for the below notation, you cant use the name of step B4 at the end of that step, it will provide cycle

B4 = Record.TransformFields (Record_Region, {"Region", (x) => Record.FieldOrDefault(repl,x)})

][B4]

---

fyi: Let expression

[Omid_Motamedise]

Hi @AlienSx 

Thanks for sharing, I know that a record definition can be used instead of let/in expression, but what happened here is different, and is the mixed.
as I knwo, the below experision doesnt mean at all (b4 is the name of step, not the field name in the record).
let
b4=[........][b4] 
in
b4

[AlienSx]

@Omid_Motamedise original code from message #1 does not have let ... in statement: 

(replacements) =>
[
// generates a record with replacements
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
Record_Region = [ Region = { "AM.", "EMEA", "APAC" }, Step = { "A", "B", "C" } ],

B4 = Record.TransformFields (Record_Region, {"Region", (x) => Record.FieldOrDefault(repl,x)})
][B4]

 so that nothing is wrong with

 [b4 = "some_value"][b4]

[Omid_Motamedise]

@AlienSx You are right, it was my mistake 

[Mic1979]

Hello AlienSx,

so what is the problem? I am sure you have suggestions to solve it (this type of structure was suggested by you in another post), because I really don't understand while I get that type of error message.

Thanks a lot.

[AlienSx · Accepted solution]

Hello, @Mic1979 first of all, what problem are you trying to solve: the one in #1 or #6? I am lost. Lets take #6 (the one with tables). You are using wrong function/approach to transform column(s) values. Why Table.TransformRows and Record.TransformFields? There is Table.TransformColumns function. 

I suggest that using record + Record.FieldOrDefault for a replacements is the "structure" you are talking about. Nothing wrong with it. It's the way you choose to apply it looks strange to me.

Okay, so be it - it's your decision. In my last message I just pointed out that list of column names (or list of single column name) should not be passed as table[column] as you suggested but as {"name1", "name2"} etc. Then you can easily setup transformations for Table.TransformColumns in the form of {{"name1", function1}, {"name2", function2}}

And even your current code works just fine if you pass {"Region"} instead of Starting_Table[Region] as you did. 

[Mic1979]

Thanks AlienSx,

it works now!!

[Mic1979]

Thanks for your feedback.

This is my Replacement_table

Old_Name      New_Name
AM.                AMERICAS
APAC              ASIA
EMEA             EUROPE

and this is my starting table:

Region      Project_Step
AM.           Step 1
EMEA        Step 2.1
APAC         Step 2.2

I would like to replace values in my column name Region.

So I have a custom function:

(data, replacements, columns_list as list) =>
[
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),

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

replace = Table.TransformRows(
data,
(x) => Record.TransformFields(x, transformations)
),

result = Table.FromRecords(replace)
][result]

invoking in this way:

let
Source = Query1_3(Starting_Table, Replacement_Table, Starting_Table[Region])
in
Source

but I have this error:

Could you help me?

Thanks.

[ronrsnfld]

One way to accomplish this task is by using the Table.ReplaceValue method as demonstrated below:

let
    Source = #"starting table",
    
    #"Replace Region" = Table.ReplaceValue(
        Source,
        each [Region],
        null,
        (x,y,z)=> Table.SelectRows(Replacement_table, each [Old_Name]=y)[New_Name]{0}? ??x,
        {"Region"}
    )
in
    #"Replace Region"

Starting table

Replacement Table

Results

[Mic1979]

Hello,

what is the meaning of the "??"?

and why the structure I used does not work fine?

Thanks.

[AlienSx]

---

@Mic1979 wrote:

and why the structure I used does not work fine?

---

Query1_3(Starting_Table, Replacement_Table, {"Region"})

[ronrsnfld]

---

@Mic1979 wrote:

what is the meaning of the "??"?

---

The item selector, expressed as {x}? will return null if the item doesn't exist.

?? is the Coalesce operator meaning if the expression on the left is null, it will return the expression on the right.

It is a shorter way of writing  if there is no matching entry in the replacement table, return the existing value in starting table 

See M Language Operators for a more complete explanation.

---

@Mic1979 wrote:

and why the structure I used does not work fine?

---

Haven't looked at that closely, but it you are not passing the correct argument to the Record.TransformFields function. I'll look at it in more detail later.

[AlienSx]

Record.FieldOrDefault expects text as 2nd parameter. Your x is a list instead (a value of Region field of Record_Region record).  

[Anonymous]

Your Record.TransformFields function requires a column name as text for the second parameter, but you have a list instead. Try using just "Region"... instead of {"Region",...}

--Nate

[Anonymous]

Yup im wrong. 

[ronrsnfld]

Kindly provide a representative sample of your replacements table. Preferably as text which can be copy/pasted.