Employee List and Transfer Posting Count

[afeer]

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 ID	Section		Employee ID	Transfer Posting
1250	Accounts		1250	4
1251	Finance		1251	2
1252	Marketing		1252	1
1253	Management		1253	1
1254	Front Desk		1254	1
1250	Marketing		1255	1
1255	Store		1256	1
1250	Front Desk
1251	Marketing
1256	Accounts
1250	Accounts

[BA_Pete · Accepted solution]

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

[v-xinruzhu-msft · Accepted solution]

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.

[BA_Pete · Accepted solution]

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