cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

 

1 ACCEPTED SOLUTION
Frequent Visitor

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!

🙂

View solution in original post

10 REPLIES 10
Frequent Visitor

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!

🙂

View solution in original post

Super User III
Super User III

@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 

SU18_powerbi_badge

Helper V
Helper V

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

Super User III
Super User III

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 

SU18_powerbi_badge

Frequent Visitor

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

 

TablePBI.jpg

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

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!

@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 

 

SU18_powerbi_badge

 

Frequent Visitor

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors