Forum Discussion
List.Select using a list from column names. Count ocurrences in columns starting with string.
Hi
Im trying to count Yes/No/NA occurrences in the columns that starts with a scpecific string. Doing in this way will permit use the same code in tables with different number of columns.
If the columns are hardcoded, it works. I get a new calculated column with the count of "Not Applicable" in this example.
QGROUP1.CountNA = Table.AddColumn(GROUP1.CountNo, "GROUP1.CountNA", each List.Count(List.Select({[GROUP1.1], [GROUP1.2], [GROUP1.3], [GROUP1.4], [GROUP1.5], [GROUP1.6], [GROUP1.7], [GROUP1.8]},each _ = "Not Applicable")),Int64.Type),
But I fail trying to generate dinamically the columns name list
{[GROUP1.1], [GROUP1.2], [GROUP1.3], [GROUP1.4], [GROUP1.5], [GROUP1.6], [GROUP1.7], [GROUP1.8]}
Two approaches
MyList1= List.Select(Table.ColumnNames(#"Reordered Columns"), each Text.StartsWith(_, "GROUP1")),
MyList2=List.Transform(MyList1, each "[" & _ & "]"),
MyList1 generates succesfully a list with the column names starting with GROUP1
MyList2 is the same list with the brackets added for each item.
But the following fails, it counts 0 using MyList1 or MyList2. Using the hardcoded list obviously works, like N/A case above.
GROUP1.CountYes = Table.AddColumn(#"Reordered Columns", "GROUP1.CountYes", each List.Count(List.Select(MyList1,each _ = "Yes")),Int64.Type),
GROUP1.CountNo = Table.AddColumn(GROUP1.CountYes, "GROUP1.CountNo", each List.Count(List.Select(MyList2,each _ = "No")), Int64.Type),
My guess is that is something related to the type of the items in the list - text?
Any help is appreciated
Thank you very much
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!
🙂
10 Replies
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!
🙂
- AlB
Community Champion
Hi CarlosFF
Can you share the table (a few rows would suffice) you are trying to apply this on? So that we can copy the contents and run a couple of tests.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi. The table looks aprox like this.
I am sorry but I do not see how to upload the table. It is my first post in this forum 😞 🙂
- AlB
You can just paste it here as normal text. Use the copy table option in PQ an paste here.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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
