CODEWITHSUNDEEP
excelpowerquerym
Naama Efrati
Naama Efrati

Reputation: 61

Power Query - Count non-null values only in columns their name starts with "Sprint_"

I have a dynamic CSV file i'm getting from JIRA, in which I have some static columns, with speified names, but some dynamic columns, their names always start with "Sprint_" and end with integer. I cannot know how many such columns will be in the file, and what will be their exact names.

Using power query, after reading the file, I now want to do two things:

  1. Count the non-null values for the "Sprint_" columns only

  2. Remove all "Sprint_" columns from my table

This one counted the "Sptint_" columns

= List.Count(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Sprint_")))

This one counted the non-null values in the whole table.

= Table.AddColumn(Source, "Count",each List.NonNullCount(Record.FieldValues(_)))

Couldn't figure out how to combine them both.

I tried replacing the Record.FieldValues() part with the other code part, but there was a mismatch between records and lists and types. I know how to count all non-null values in a row, I know how many Sprint columns I have, but I don't know how to count all non-null value only in these Sprint_ columns.

Upvotes: 3

Views: 934

Answers (1)

Naama Efrati
Naama Efrati

Reputation: 61

Managed with some help in another forum:

=  Table.AddColumn(#"Previous step","Count Sprints", each List.NonNullCount(Record.ToList(Record.SelectFields(_,List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Sprint_"))))))

Upvotes: 3

Related Questions