Count non-null rows in a group

[CharC]

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

Name	Item
James	apple
Sarah
Charles	orange
Charles	graphs

Desired output:

Name	Item	Count item
James	apple	1
Sarah		0
Charles	orange	2
Charles	graphs	2

[BA_Pete · Accepted solution]

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]

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

([data][Item])

[rohit1991]

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] ) = ""       
            )
    )
)