Forum Discussion

afeer's avatar
afeer
New Member
2 years ago
Solved

Employee List and Transfer Posting Count

I have a data set of many tables and columns, in which one column is "Employee ID" and another column which keeps record of any transfer posting of Employee from one section to another in a filed name "Emplyee Section".

Emplyee ID  | Employee Section

1250  |  Acounts

1251  |  Finance

1252  |  Marketing

1250  |  Marketing

1250  | Front Desk

1253  | Managment

 

I need to generate a New Table with two column for 

Employee ID | Transfer Posting Count

 

to check frequent transfer postings.

 

Employee IDSection Employee IDTransfer Posting
1250Accounts 12504
1251Finance 12512
1252Marketing 12521
1253Management 12531
1254Front Desk 12541
1250Marketing 12551
1255Store 12561
1250Front Desk   
1251Marketing   
1256Accounts   
1250Accounts   

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    Hi afeer ,

     

    Select your [Employee ID] column, go to the Home tab > Group By.

    For the aggregated column at the bottom, leave the operator as Count.

     

    Pete

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi afeer 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNVDSUXJMTs4vzSspVorVAYsZAsXcMvMS85JTYUJGQCHfxKLs1JLMvHSYoDFYMC8xPTU3Na8EJmoC0l2Un1ei4JJanA0TNcBmgClQMLgkvygVWRWmXkNses2wOBzVM7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Section = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Section", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee ID"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

    Output

    Best Regards!

    Yolo Zhu

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

     

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    Hi afeer ,

     

    Select your [Employee ID] column, go to the Home tab > Group By.

    For the aggregated column at the bottom, leave the operator as Count.

     

    Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi afeer 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNVDSUXJMTs4vzSspVorVAYsZAsXcMvMS85JTYUJGQCHfxKLs1JLMvHSYoDFYMC8xPTU3Na8EJmoC0l2Un1ei4JJanA0TNcBmgClQMLgkvygVWRWmXkNses2wOBzVM7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Section = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Section", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee ID"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

    Output

    Best Regards!

    Yolo Zhu

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