Forum Discussion
List.Select using a list from column names. Count ocurrences in columns starting with string.
Hi.
I have found this approach in
MyCols = List.Buffer(List.Select(Table.ColumnNames(#"Reordered Columns"), each Text.StartsWith(_, "GROUP_"))), GROUP_1_Yes = Table.AddColumn(#"Reordered Columns" , "GROUP_1_Yes", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _ = "Yes")) ,Int64.Type),Thank you all for your help!
🙂
If it's a matter of having more or less columns, but the column names themselves do not change, then you can have a list of all the possible columns in a list in a separate query (perhaps using Table.ColumnNames(TableWithAllTheColumns), but get them all in a list, named Columns. Then just:
Table.SelectColumns(TableName, Columns, MissingField.Ignore)
The MissingField.Ignore parameter will look for all of the columns in the Columns list, and if the columns do not exist, they get ignored. Those that do exist get selected.
--Nate
It is a nice option for your scenario where names do not change. In my tables the columns to be used start with a specific string but they are different in every table. Table 1 (GROUP1.1 GROUP1.2) Table 2 (GROUP2.1 GROUP2.2)
Thanks!
Best regards