Forum Discussion
How to calculate Head Count in Power Query
I need to be able to calculate the Head Count for my organisation on Power Query. I have received help from the community and believed I had a solution but unfortunately I have encountered a glitch. For staff that have one post only I need to record "1". For staff that have 2 or more posts, I need to record "1" only against the post with the highest number of hours (WTE). The solution provided by the community allows me to do that but the issue is where staff have a temporary post and a permanent post and have the same number of hours (WTE) then it is defaulting to place the "1" against the temporary post. In my headcount, in this scenario it needs to default to permanent, but only in that scenario.
In the solution that has a slight glitch, I group the data by Tax Code then add a conditional column with the following M code
Table. Sort([All Data], {"WTE", Order. Descending}) then add another additional column with the following code to Index the data
Table. Add Index Column([All Data Sort],"Index") then once the data has been expanded, I add another column stating that if Index = 0 then 1 otherwise 0. As previously stated, this works perfectly until we get to the scenario where someone has two jobs, with the same hours (WTE) but one job is permanent and the other temporary. I have tried to amend the indexing and tried sorting the data in several ways but just cant seem to crack this nut. Any help would be greatly appreciated. I have attached sample data to show how it currently looks and how I need it to look.
Many thanks.
Ciaran
24 Replies
Use below query. Solution file uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuTFskZVVvGgDjnmE?e=YrZgL8
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Employee"}, {{"Temp", each _, type table [Dept=number, WTE=number, Duplicate NI No=text, Tax Code=text, Employee=text, #"Contract "=text, Index=number]}}), //Function Start fxProcess=(Tbl)=> let #"Added Custom" = Table.AddColumn(Tbl, "Headcount", each if List.Count(List.Select(Tbl[WTE], each _ = List.Max(Tbl[WTE]))) > 1 then if List.Max(Tbl[WTE])=[WTE] and [Contract]="Permanent" then 1 else 0 else if List.Max(Tbl[WTE])=[WTE] then 1 else 0) in #"Added Custom", //Function End #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])), #"Expanded fxProcess" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcess", {"Dept", "WTE", "Duplicate NI No", "Tax Code", "Contract", "Headcount"}, {"Dept", "WTE", "Duplicate NI No", "Tax Code", "Contract", "Headcount"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}) in #"Removed Columns"Hi Vijay, apologies for my ignorance but I am new to Power Query, would you be able to show me how to apply this coding as I am totally lost how to apply your M code. Sorry for any inconvenience.
Open the Excel file uploaded by me @ https://1drv.ms/x/s!Akd5y6ruJhvhuTFskZVVvGgDjnmE?e=YrZgL8 - Data tab - Queries and Connections - On right side pane, double clik on Table1 to see the Steps and result.
