Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

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 nam...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago

    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's avatar
Mic1979
Post Partisan
1 year ago

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's avatar
    ronrsnfld
    Super User
    1 year ago

    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's avatar
      Mic1979
      Post Partisan
      1 year ago

      Hello,

       

      what is the meaning of the "??"?

      and why the structure I used does not work fine?

       

      Thanks.

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        1 year ago
        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.