List.Select using a list from column names. Count ocurrences in columns starting with string.

[CarlosFF]

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

[CarlosFF · Accepted solution]

Hi.

I have found this approach in 

https://www.mrexcel.com/board/threads/better-power-query-formula-to-get-a-list-of-table-columns-name... 

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]

@CarlosFF 

what HTML error exactly? Where?

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 

[watkinnc]

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

[CarlosFF]

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

[AlB]

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 

[CarlosFF]

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 😞  🙂

[CNENFRNL]

Hi, @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),

[CarlosFF]

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!

[AlB]

@CarlosFF 

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 

[CarlosFF]

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