Headcount problem

[Syndicate_Admin]

Id be grateful if someone could help me solve this issue. I have to create a Departmental headcount and the scenario is that employees can have multiple employments.  If the person is employed once then it is a straightforward headcount of 1.  If the person is employed more than once but in the same Dept then the headcount is recorded as 1 only.  If the person has multiple jobs and is employed in differing depts then 1 is recorded for each job. I have attached an image to help visualise the query and also attached the  data.

In Excel we sort the data by Duplicate Tax No then by Tax Code, then by Trust H/C and finally by Dept and add the formula

=IF(A2=A1,”0”,1) whereby column A is the dept. Any help would be greatly appreciated as I have exhausted Youtube trying to teach myself.  Thank you.

[Vijay_A_Verma]

Here is slightly different approach using Group By

https://1drv.ms/x/s!Akd5y6ruJhvhuRn9oLqJ0qqCTRlp?e=f9vbSU 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Employee", type text}, {"Tax Code", type text}, {"Duplicate Tax Code", type text}, {"Trust Head Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Dept", "Employee"}, {{"Temp", each _, type table [Dept=nullable text, Employee=nullable text, Tax Code=nullable text, Duplicate Tax Code=nullable text, Trust Head Count=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Temp],"Index")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Tax Code", "Duplicate Tax Code", "Trust Head Count", "Index"}, {"Tax Code", "Duplicate Tax Code", "Trust Head Count", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Dept Head Count", each if [Index]=0 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Temp"})
in
    #"Removed Columns"

[v-angzheng-msft · Accepted solution]

Hi, @Syndicate_Admin 

Try this:
M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktKFEwVNJR8i1ScARSjo4g0i8fSBgqxepAFRiBFRQrOAFpJycQGZlaTJwSA4QSY6gSZyDt7OyMzRQTHEqQTDHFrSQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, Employee = _t, #"Tax Code" = _t, #"Duplicate Tax Code" = _t, #"Trust Head Count" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}, {"Employee", type text}, {"Tax Code", type text}, {"Duplicate Tax Code", type text}, {"Trust Head Count", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Employee", "Dept"}, #"Added Index", {"Employee", "Dept"}, "Added Index", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Result", each if [Index] = List.Min([Added Index][Index]) then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{ "Index", "Added Index"})
in
    #"Removed Columns"

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You can use a measure expression like the one below to get the distinctcount of employee and dept combinations.

Employee Dept Count = COUNTROWS(SUMMARIZE(Table, Table[Employee], Table[Dept]))

Pat

[Syndicate_Admin]

Hi Pat, thank you for you reply however, when I try to run that expression I get countrows wasn't recognized.