Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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 t...
  • 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.

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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"