Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Headcount problem

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.

 

  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      4 years ago

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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"