Countif (or equivalent) Per Row in PowerQuery/Dax

[AndrewKent]

Hi,

Apologies but although I can see several topics on this, I'm not sure how they fit to my specific issue. I have one table of data that I am modelling. The table contains records assessment attempts for staff. There are multiple rows for individuals. What I want to do is:

- In a new column [Attempts], carry out a countif on a column [Staff ID] for each row in the table (the Excel equivalent would be =COUNTIF([STAFF ID],A1) for each row.

- Remove duplicate rows in [Staff ID]

This should leave me with 1 record per staff member with an aggregated amount of of attempts in the [Attempts] column

Hope this makes sense.

Andy

[hadiahadi]

take a look here:

https://stackoverflow.com/a/48782135/9187705

[hadiahadi]

see here:

https://stackoverflow.com/a/48782135/9187705

[BhaveshPatel]

I think You can fit Conditional column and Groupby in this scenario. 

Sample data would give me a kick start to work with your problem.

[AndrewKent]

Hi,

The data set really is as simple as the first data set is the original data, and using Power Query I want to model it to look like the second table if that makes sense.

Andy

[sgoyal]

hi,

I am looking for countif to work same as it work in excel. so, each row would have distinct count

I am new to power query pls help in how to add column and add function. 

Thanks in advance

[BhaveshPatel · Accepted solution]

Hi Andy

Doing a Groupby can solve your problem. See the attached screenshot.GROUPBY

EXPECTED OUTPUT

[AndrewKent]

Perfect! Exactly what I was after.