formula in M language to calculate the max of dynamic list of columns in a new Column

[Bassil74]

I have a table called Indicators  withpivoted MPs, i.e. 1,2,3... 8  and this list will expand so MP9, 10...etc. 

i want to create a new column in the same Indicators table that calculate the max of the values for columns 1...8 which is taken from another table called Open_MPs which has recent values.    

So basically i need a way to List.Max with list of columns fetched from another table. Ca someone help me?

Appreciate it guys! 

[AlienSx · Accepted solution]

Table.AddColumn(
  Indicators, "max", 
  (x) => 
    List.Max(
      Record.FieldValues(
        Record.SelectFields(x, Open_MPs[Open_MPs])
      )
    )
)

[AlienSx · Accepted solution]

@Bassil74 List.RemoveNulls helps

[slorin · Accepted solution]

Hi, @AlienSx 

let functions =
 [Sum="List.Sum",Max="List.Max", Min="List.Min", Average="List.Average",Latest Value="List.Last"],
 result = Function.Invoke(
    Expression.Evaluate(Record.Field(functions, [calculationMethod]), #shared) ,
    {List.RemoveNulls(Record.FieldValues(Record.SelectFields(_,Open_MPs[OpenMPs])))}
    )
 in result

Stéphane

[Bassil74]

Thank you @AlienSx  this works perfectly, i also further developed it to include multiple calculations as below

= Table.AddColumn(source, "Result", each

        let

            method = [calculationMethod],

            sumVal = List.Sum(Record.FieldValues(Record.SelectFields(_, Open_MPs[openMPs]))),

            maxVal = List.Max(Record.FieldValues(Record.SelectFields(_, Open_MPs[openMPs]))),

            minVal = List.Min(Record.FieldValues(Record.SelectFields(_, Open_MPs[openMPs]))),

            avgVal = List.Average(Record.FieldValues(Record.SelectFields(_, Open_MPs[openMPs]))),

            latestVal = List.Last(Record.FieldValues(Record.SelectFields(_, Open_MPs[openMPs])),-1)

        in

            if method = "Sum" then sumVal

            else if method = "Maximum value" then maxVal

            else if method = "Minimum value" then minVal

            else if method = "Average" then avgVal

            else if method = "Latest value" then latestVal

            else null

    )

which is working correctly. The one issue i faced is that one null value for the List.Last in the above script will dominate and return null even though other columns included in the calculations has actuall values. List.Average for example works correctly i.e. it doesn't include null values in the calcs. 

[AlienSx · Accepted solution]

@Bassil74 List.RemoveNulls helps

[Bassil74]

Thank you so much @AlienSx  this worked perfect! 

[AlienSx]

@Bassil74 also, try this instead

let
    functions = 
        [Sum = "List.Sum", 
        Maximum value = "List.Max", 
        Minimum value = "List.Min",
        Average = "List.Average",
        Latest value = "List.Last"],
    result = 
        Expression.Evaluate(
            Record.Field(functions, [calculationMethod]) & 
            "(List.RemoveNulls(Record.FieldValues(Record.SelectFields(_, Open_MPs[openMPs]))))"
        )
in
    result

[Bassil74]

This is working perfect. Now something new introduced, how to add to include to this code if [calculationMethod] = null then to run List.Last

= Table.AddColumn(Pivot_MPs, "Result", each
try Function.Invoke(
Record.Field(
[Sum=List.Sum, Maximum value=List.Max, Minimum value=List.Min, Average=List.Average, Latest value=List.Last, Manual value=List.Last],
[calculationMethod]),
{List.RemoveNulls(Record.FieldValues(Record.SelectFields(_,Open_MPs[openMPs])))})
otherwise null) 

[slorin · Accepted solution]

= Table.AddColumn(Pivot_MPs, "Result", each
try Function.Invoke(
Record.Field(
[Sum=List.Sum, Maximum value=List.Max, Minimum value=List.Min, Average=List.Average, Latest value=List.Last, Manual value=List.Last],
[calculationMethod]??"Manual value"),
{List.RemoveNulls(Record.FieldValues(Record.SelectFields(_,Open_MPs[openMPs])))})
otherwise null) 

Stéphane 

[Bassil74]

I want to feedback on teh above, i found my query, which connects to API source, became very slow running the above part, it takes more than 15 mins to makethe calculations of ~ 200 records so i wonder if there is more efficient method than the above?    

[Bassil74]

this is A M A Z I N G. thank you so much @Stéphane 

[Bassil74]

I tried the the reviewed code but it is throwing this error

Expression.Error: [1,1-1,10] The name 'List.Last' doesn't exist in the current context.
Details:
[List]

[AlienSx]

@Bassil74 my bad, looks like it's not that easy as I expected. Have to get back to school again and learn more about environments in M. Disregard my code, please. 

[slorin · Accepted solution]

Hi, @AlienSx 

let functions =
 [Sum="List.Sum",Max="List.Max", Min="List.Min", Average="List.Average",Latest Value="List.Last"],
 result = Function.Invoke(
    Expression.Evaluate(Record.Field(functions, [calculationMethod]), #shared) ,
    {List.RemoveNulls(Record.FieldValues(Record.SelectFields(_,Open_MPs[OpenMPs])))}
    )
 in result

Stéphane

[Bassil74]

This is working code, thank you @slorin . except that i gort this error message due to null values in the field [calculationMethod]:

We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

[slorin]

Use try ... otherwise

Solution without Expression.Evaluate

try Function.Invoke(
    Record.Field(
      [Sum=List.Sum, Max=List.Max, Min=List.Min, Average=List.Average, Latest=List.Last],
      [calculationMethod]),
    {List.RemoveNulls(Record.FieldValues(Record.SelectFields(_,Open_MPs[OpenMPs])))})
otherwise null

 Stéphane

[Bassil74]

Thank you Stéphane, iam getting error at the try step when tried to implement your proposal

[slorin]

... each

let

try ...

[Bassil74]

Thank you AlienSx, well done

[AlienSx · Accepted solution]

Table.AddColumn(
  Indicators, "max", 
  (x) => 
    List.Max(
      Record.FieldValues(
        Record.SelectFields(x, Open_MPs[Open_MPs])
      )
    )
)