Rowwise minimum without exact column name

[tt211595]

Hello! Say I have a table with three columns (ID, Column2, and Column3). I'd like to add a new column named Column4 that has a row-wise minimum calculated using Column2 and Column3. Normally, in Power Query, I'd do something like this:

new_column = Table.AddColumn(Source, "Column4", each List.Min({[Column2], [Column3]}), Int64.Type)

Is there a way I can do the same thing without using the exact column names? For example, instead of listing [Column2] and [Column3] in List.Min(), what if I wanted to calculate row-wise minima across all columns containing "Column" in the header with something like Text.Contains()?

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    selectedheaders = List.Select(Table.ColumnNames(#"Changed Type"),each Text.Contains(_ ,"Column")),
    Custom1 = #"Changed Type",
    #"Added Custom" = Table.AddColumn(Custom1, "Min value", each List.Min(Record.ToList(Record.SelectFields(_,selectedheaders))), type number)
in
    #"Added Custom"

Hope this helps.

[tt211595]

This is perfect. Thanks!

[Ashish_Mathur]

You are welcome.

[Ahmedx]

Pls try this

List.Min({
Record.FieldValues(_){1},
Record.FieldValues(_){2}} 
 )

[tt211595]

Thank you, this is helpful!