How to calculate WTE for Duplicate Records

[Syndicate_Admin]

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 solution]

Hi,

to get 

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

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Vijay_A_Verma]

Attachment missing. Please re-attach.