Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Aggregate Dates by Sum or Count in Power Query

I'm not sure how to solve this one and could use some help from the Power BI Community...

 

I'm hoping to aggregate the dates into a count, with null showing a 0. So if someone has two dates associated with their employee ID, they should show a 2 in the training completed column. There are other pieces of information in this table which all have 1 row of information per Employee ID - so the entire table should remain. Id like to solve this in Power Query.

 

I have a table:

Employee ID  |  Training Completed date  | 10 more columns on other things about employee

251                 | null

542                 | 1/23/2019

542                 | 2/27/2019

693                 | null

002                 | 3/31/2019

 

I'd like it to look like:

Employee ID

251               | 0

542               | 2

693               | 0

002               | 1