Forum Discussion
Record.TransformFields error
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.
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.
17 Replies
Kindly provide a representative sample of your replacements table. Preferably as text which can be copy/pasted.
Record.FieldOrDefault expects text as 2nd parameter. Your x is a list instead (a value of Region field of Record_Region record).
Thanks for your feedback.
This is my Replacement_table
Old_Name New_Name
AM. AMERICAS
APAC ASIA
EMEA EUROPEand this is my starting table:
Region Project_Step
AM. Step 1
EMEA Step 2.1
APAC Step 2.2I 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
Sourcebut I have this error:
Could you help me?
Thanks.
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
Hello,
what is the meaning of the "??"?
and why the structure I used does not work fine?
Thanks.
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]
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
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.
