Forum Discussion

Thomas_123's avatar
Thomas_123
New Member
1 year ago
Solved

Replace fixed column names in a function

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

  • 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?

2 Replies

  • 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's avatar
      Thomas_123
      New Member

      Hello Owen

       

      Great! It is working. You saved my Sunday.