Forum Discussion
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 MyFunctionDoes this work for you?
2 Replies
- OwenAugerSuper User
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 MyFunctionDoes this work for you?
- Thomas_123New Member
Hello Owen
Great! It is working. You saved my Sunday.