Forum Discussion
Rowwise minimum without exact column name
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!
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.
5 Replies
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.
This is perfect. Thanks!
You are welcome.
Pls try this
List.Min({ Record.FieldValues(_){1}, Record.FieldValues(_){2}} )Thank you, this is helpful!
