Count non-null rows in a group

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