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

[naamaef]

Hello, 

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.

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.

[m_dekorte · Accepted solution]

Hi @naamaef 

Here's an example.

let
    // create a list with Sprint_ column names
    ListSprintCols = List.Select( Table.ColumnNames(Source), each Text.StartsWith( _, "Sprint_")),
    Source = Table.FromRows(
        {
            { "a".. "c" } & { 1..7 },
            { "d".. "f" } & { 2..8 },
            { "g".. "i" } & { 3..9 }
        },
        List.Transform( { "1".."3" }, each "Column" & _ ) &
        List.Transform( { "1".."7" }, each "Sprint_" & _ )
    ),
    InsertNulls = Table.ReplaceValue(Source,null,null,(a, b, c)=> if (( Number.IsEven(a) and Number.Mod(a,3)=0) or a=2) then null else a, ListSprintCols ),
    // add a non-null count for all Sprint_ fields
    AddCount = Table.AddColumn(InsertNulls, "SprintCol Non-null Count", each 
        List.NonNullCount( Record.ToList( Record.SelectFields( _, ListSprintCols ))), Int64.Type
    ),
    // remove the Sprint_ columns
    SelectCols = Table.RemoveColumns( AddCount, ListSprintCols )
in
    SelectCols

The key is creating a separate ListSprintCols step, that you can use that over and over again. Like to select fields from the record and to remove columns from the table.

Ps. If this helps you to solve your query, please mark this post as solution. Thanks!

[ronrsnfld]

Add an index column so you can refer to the entire row. Then use Record.SelectFields to pick out the ones that start with your key.

[naamaef]

I'm sorry, I don't think I get this. Too new in this. Can you send a sample command?