Replace fixed column names in a function

[Thomas_123]

Hello

I try to replace fixed column names in a function to reuse the function in other queries.

Unfortunately I can't get any further with two replacements.

The function calculates an average based on two given columns (YearColumn, ValueColumn) and a number of years looking backwards (Years_back).

In lines 10 to 12 I was able to replace the original fixed column names.

The issues are in row 13 "row [YearColumn]" and row 14 "[ValueColumn]". If I use the original fixed names than it is working.

let

    MyFunction = (Table as table, NewColumn as text, YearColumn as text, ValueColumn as text, Years_back as number) =>
    
    Table.AddColumn(Table,NewColumn,
                            (x) => List.Average(
                                                Table.SelectRows( Table,
                                                                 (row) => List.Contains( 
                                                                                        List.MaxN( 
                                                                                                List.Select( Table.Column( Table, YearColumn ), 
                                                                                                                (y) => y <= x {YearColumn} ), 
                                                                                                                Years_back), 
                                                                            row [YearColumn]  )
                                                                ) [ValueColumn]
                                                )
                    )

in 

MyFunction

Here comes the original embedded function.

    Custom = Table.AddColumn(Prev_Step,"AVG",
                                (x) => List.Average(
                                                    Table.SelectRows( Prev_Step,
                                                                    (row) => List.Contains( 
                                                                                        List.MaxN( 
                                                                                                    List.Select(Prev_Step[Year], 
                                                                                                    (y) => y <= x[Year]),
                                                                                                    5),
                                                                                row[Year])
                                                                    )[#"Values"]
                                                    )
                                        )

Best regards

[OwenAuger · Accepted solution]

Hi @Thomas_123 

You can update those pieces of code using

Table.Column( ... , ValueColumn )

and

Record.Field ( row, YearColumn) 

The full M code (reformatted slightly) would be:

let
  MyFunction = ( Table as table, NewColumn as text, YearColumn as text, ValueColumn as text, Years_back as number ) =>
    Table.AddColumn(
      Table,
      NewColumn,
      (x) =>
        List.Average(
          Table.Column(
            Table.SelectRows(
              Table,
              (row) =>
                List.Contains(
                  List.MaxN(
                    List.Select(Table.Column(Table, YearColumn), (y) => y <= x{YearColumn}),
                    Years_back
                  ),
                  Record.Field(row, YearColumn)
                )
            ),
            ValueColumn
          )
        )
    )
in
  MyFunction

Does this work for you?

[Thomas_123]

Hello Owen

Great! It is working. You saved my Sunday.