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!
🙂
Community ChampionHi 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
Nice feature. It pastes correctly but when saving gives an HTML error. I have tried copying from excel and power Bi. No way. Also doing the table manually. Weird.
Best regards
CNENFRNLHi, CarlosFF , it's simple enough like this,
GROUP1.CountYes = Table.AddColumn(#"Reordered Columns", "GROUP1.CountYes", each List.Count(List.Select(Record.ToList(_),each _ = "Yes")),Int64.Type), GROUP1.CountNo = Table.AddColumn(GROUP1.CountYes, "GROUP1.CountNo", each List.Count(List.Select(Record.ToList(_),each _ = "No")), Int64.Type),Hi. It looks simple.
Where you put the list name?
GROUP1.CountNo = Table.AddColumn(GROUP1.CountYes, "GROUP1.CountNo", each List.Count(List.Select(Record.ToList(MYLIST),each _ = "No")), Int64.Type),
As argument of Record.ToList it gives the following error, using MyList1 or MyList2 (items with brackets)
Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=[List]
Type=[Type]
Thanks!