GROUP BY category with not null counts

[Anonymous]

 I have a table with three columns(Team name, created date, and completed date)

team	created	completed
A	1/1/2022	1/1/2022
A	1/3/2022	null
A	1/3/2022	1/3/2022
A	1/3/2022	null
A	1/5/2022	1/5/2022
R	1/1/2022	1/1/2022
R	1/3/2022	1/3/2022
R	1/3/2022	null
R	1/3/2022	1/3/2022
T	1/5/2022	1/5/2022
T	1/1/2022	null
T	1/3/2022	1/3/2022

what i want to do is group by team  and get the created and complted tickets counts. Like below result i only need to get the count for dates not null values,

ex: team A created 5 tickets but complted only 3 because of two null values.

Expected output:

team	created	completed
A	5	3
R	4	3
T	3	2

Tried to get this usging power query group by but didnt get the expected output.

[Ashish_Mathur]

Hi,

Try these measures

Created = countrows(Data)

Completed count = calculate([Created],Data[completed]<>blank())

Hope this helps.

[Anonymous]

Thanks. but how do i group by teams?

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

= Table.Group(#"Changed Type", {"team"}, {{"Count", each List.NonNullCount([created]), Int64.Type}, {"Count2", each List.NonNullCount([completed]), Int64.Type}})

pls see the attachment below

[Anonymous]

Thanks. Did u do the above code by clicking "Group By" button or just using the code?

[ryan_mayu]

you can click group by and select count first, then update the formula.