Forum Discussion
GROUP BY category with not null counts
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.
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
5 Replies
ryan_mayuSuper User
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
- ryan_mayu
you can click group by and select count first, then update the formula.
- Ashish_Mathur
Hi,
Try these measures
Created = countrows(Data)
Completed count = calculate([Created],Data[completed]<>blank())
Hope this helps.
