Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to 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
Proud to be a Datanaut!
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.
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.
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
Proud to be a Datanaut!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
13 | |
12 | |
12 |