Forum Discussion
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 Name Item James app...
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 expandNestedItemSummary:
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
