Forum Discussion

CharC's avatar
CharC
Frequent Visitor
1 year ago
Solved

Count non-null rows in a group

Hi, I'm not very good at using the group by function. I want to add a column to my table to flag how many non-null rows each group has, example below. 

 

Original table

NameItem
Jamesapple
Sarah 
Charlesorange
Charlesgraphs

 

Desired output:

 

NameItemCount item
Jamesapple1
Sarah 0
Charlesorange2
Charlesgraphs2
Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    Hi CharC ,

     

    To do this in Power Query, try the following example code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lFKLCjISVWK1YlWCk4sSswAiiiAec4ZiUU5YBX5RYl56alogulFiQUZxUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Item = _t]),
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Item"}),
    groupName = Table.Group(repBlankNull, {"Name"}, {{"data", each _, type table [Name=nullable text, Item=nullable text]}}),
    addNonNullCount = Table.AddColumn(groupName, "NonNullCount", each List.NonNullCount([data][Item])),
    expandNestedItem = Table.ExpandTableColumn(addNonNullCount, "data", {"Item"}, {"Item"})
in
    expandNestedItem

     

    Summary:

    repBlankNull (not essential): Ensures cells are actually null, not just blank ("")

    groupName: Group table on [Name] column, using 'All Rows' aggregator

    addNonNullCount: Add new column to count non-null values in nested tables

    expandNestedItem: Expand the [Item] column back out from the nested tables.

     

    Output:

     

    Pete

3 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi CharC 

    Could you please try below Calculated Column: 

    Count item = 
VAR CurrentName = 'Sample Data'[Name]
RETURN
COUNTROWS (
    FILTER (
        ALL ( 'Sample Data' ),
        'Sample Data'[Name] = CurrentName
            && NOT (
                ISBLANK ( 'Sample Data'[Item] )
                || TRIM ( 'Sample Data'[Item] ) = ""       
            )
    )
)

     

     

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

    Hi CharC ,

     

    To do this in Power Query, try the following example code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lFKLCjISVWK1YlWCk4sSswAiiiAec4ZiUU5YBX5RYl56alogulFiQUZxUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Item = _t]),
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Item"}),
    groupName = Table.Group(repBlankNull, {"Name"}, {{"data", each _, type table [Name=nullable text, Item=nullable text]}}),
    addNonNullCount = Table.AddColumn(groupName, "NonNullCount", each List.NonNullCount([data][Item])),
    expandNestedItem = Table.ExpandTableColumn(addNonNullCount, "data", {"Item"}, {"Item"})
in
    expandNestedItem

     

    Summary:

    repBlankNull (not essential): Ensures cells are actually null, not just blank ("")

    groupName: Group table on [Name] column, using 'All Rows' aggregator

    addNonNullCount: Add new column to count non-null values in nested tables

    expandNestedItem: Expand the [Item] column back out from the nested tables.

     

    Output:

     

    Pete

    • CharC's avatar
      CharC
      Frequent Visitor
      1 year ago

      Thank you. In my original code I missed the table reference ([data]) and couldn't get the results right. 

      ([data][Item])