Forum Discussion
Count different values in different columns, based on an equal date.
Hi,
To give you some background information:
I'm trying to report the number of Job Requisitions (Job Req ID) who where published (job post name) internally (Job Posting: Internal posting) and externally (Job Posting: External Posting) on the same date (Posting Start Date).
Example:
In this example following job req id's should be counted:
- 104588 = 1 double posting
- 94584 = 1 double posting
- 119917 = 0 double posting
- 31630 = 0 double posting
- 64128 = 0 double posting
Looking forward to your help!
Teis
In M code, you can try this
NewStep=Table.Group(PreivousStepName,"Job Req ID",{"Double Posting Count",each List.NonNullCount(Table.Group(_,"Posting Start Date",{"n",each if List.ContainsAll([Job Post Name],{"Internal",External"},(x,y)=>Text.Contains(x,y)) then 1 else null})[n])})
1 Reply
In M code, you can try this
NewStep=Table.Group(PreivousStepName,"Job Req ID",{"Double Posting Count",each List.NonNullCount(Table.Group(_,"Posting Start Date",{"n",each if List.ContainsAll([Job Post Name],{"Internal",External"},(x,y)=>Text.Contains(x,y)) then 1 else null})[n])})