Create table like Pivot table to do headcount in PowerBI

[lezawang]

Hi
I have this table

Book1.xlsx
	A	B	C	D	E
1	name	gender	dept	age	salary
2	mary1	f	IT	28	88769
3	alex1	m	Sales	36	59728
4	mike1	m	Marketing	33	19819
5	chin1	m	HR	69	89760
6	anna1	f	HR	23	40182
7	sarah1	f	IT	65	75338
8	danny1	m	Sales	28	22490
9	adam1	m	IT	46	69257
10	alii1	f	Sales	51	68221
Sheet1

What I want to create a table that shows dept and headcount for each dept like this

Book1.xlsx
	A	B
3	Row Labels	Count of name
4	HR	6
5	IT	7
6	Marketing	4
7	Sales	7
8	Grand Total	24
Sheet2

I did that using Pivot table. Now I want to user Power BI to do that. I can move the dept column to a table place holder but when I move the name to the same table, it listed all names not count of names like what pivot table does, please see the screen shot below below:

da1.JPG

drive.google.com

How can I create a table like the second one using PowerBI. Thank you very much.

 

[alansidman]

Use the Power Query functionality in PBI. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"gender", type text}, {"dept", type text}, {"age", Int64.Type}, {"salary", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"dept"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[lezawang]

Use the Power Query functionality in PBI. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"gender", type text}, {"dept", type text}, {"age", Int64.Type}, {"salary", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"dept"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Thank you very much for your help. Just a question, can it be done without writing M code? I am not familiar with M code yet. Thank you once again.

 

[alansidman]

I did not write Mcode. I used the functionality of Power Query. I selected the column of Data in the Dept Field. I then selected Group By and Had PQ then count the times the Dept appears.

the Mcode is the result of these actions in the U/I

 

[lezawang]

I did not write Mcode. I used the functionality of Power Query. I selected the column of Data in the Dept Field. I then selected Group By and Had PQ then count the times the Dept appears.

the Mcode is the result of these actions in the U/I

Thank you once again. I appreciate the help.