How to calculate WTE for Duplicate Records

Question

I have staff data that incudes staff with multiple contracts with various hours (WTE) In Excel I use CountIF to identify them and Index the WTE. I then create a new column in Excel using Count IF to add the WTEs together for the duplicate records. I have tried to replicate this on Power Query but cannot get it to work. Any help would be greatly appreciated.

Raw Data:

Data After Count Ifs are added on EXCEL

The First Count IF Identifies how many posts the person has (Head Count)

The 2nd Count IF I use in Excel adds the WTE together in the Updated WTE Column for those that are duplicate NI Nos and gives a total for those duplicates. It also gives the WTE for the non duplicates in the Update WTE column

I have attached a sample spreadsheet for ease. If anyone could help me I would b greatly appreciative.

serpiva64 · Accepted Answer

Hi,to get&nbsp;&nbsp;you can try this:- Group by- add a index- choose columns- expand- group by again- choose columns- expand- add a conditional column- finally delete unnecessary columns and reorder&nbsp;If this post is useful to help you to solve your issue consider giving the post a thumbs up&nbsp;&nbsp;and accepting it as a solution !&nbsp;&nbsp;&nbsp;&nbsp;