Power Query - Counting non blanks in column in Group function

2
votes

I'm not a pro in writing code from scratch in Power Query, rather clicking it and tweaking only to my needs and I got stuck when using Group function. There is only option of counting rows and I would like to count non blank cells in specific column.

Code looks like this:

= Table.Group(#"Replaced Value", 
     {"Company Name", "Policy Number", "Division", "Date"}, 
         {{"Count", each Table.RowCount(_), type number}, 
         {"Sum", each List.Sum([Claim Amount]), type number
         }
     })

it has to group data by four fields (Company Name, Policy Number, Division, Date) and return number of claims and sum of claims.

I tried changing it to use Counta function: https://msdn.microsoft.com/en-us/query-bi/dax/counta-function-dax example below:

= Table.Group(#"Replaced Value", 
     {"Company Name", "Policy Number", "Division", "Date"}, 
         {{"Count", each List.Counta([Claim Amount]), type number}, 
         {"Sum", each List.Sum([Claim Amount]), type number
         }
     })

and many other variations of above but without any luck. Can you help me and tell what I'm doing wrong here.

EDIT: I already considered removing nulls before grouping but I actually need information that in certain group there were 0 claims in particular month.

Thanks in advance for your help

3
excelpowerquery

3 Answers

4
votes

Assuming that your "blank" fields are in fact null (which it sounds like they are based on what you said), you can use List.NonNullCount to get to count of items in the field that are, well, not null.

= Table.Group(#"Replaced Value", 
     {"Company Name", "Policy Number", "Division", "Date"}, 
         {{"Count", each List.NonNullCount([Claim Amount]), type number}             
     })
1
votes

Try this:

=List.NonNullCount(Record.FieldValues(_))
0
votes

Why not just filter the blanks then do group?

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Claim Amount] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Company Name", "Policy Number", "Division", "Date"}, {{"Count", each Table.RowCount(_), type number}, {"Sum", each List.Sum([Claim Amount]), type number}})
in #"Grouped Rows"