Forum Discussion

naamaef's avatar
naamaef
New Member
3 years ago
Solved

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

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.

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      naamaef
      New Member
      3 years ago

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

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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!