You’re offline. This is a read only version of the page.
261
I am new to Power Query and have an issue with a calculation. My task it calculate Headcount. My issue is that I need a manner in which I can count staff once who have multiple employments.
For Example
So, If I am employed once I have a "1" recorded against me, If I am employed more than once then a "1" should be recorded based on the job that I am doing the higher number of hours in.
An employee has 2 posts both part time 30 hours and 10 hours per week however, I need a means to record a "1" against the higher number of hours and a zero against the lower number of hours (i.e. without deleting the duplicate row). In excel we use a formula to calculate this but I am lost how to do this in Power Query. Any help would be appreciated.
Hi Jing the issue only arises when a person has a Duplicate NI No i.e. a "Yes" value recorded and they have temporary and permanent posts with the same WTE.
In the example Mr D has a permanent post and a temporary post so has a Duplicate NI No recorded as a YES value & in both posts he has 0.5 WTE. I need to ensure that where this happens that it always choses the permanent post but only where the person has a Duplicate NI No, where they have temporary and permanent posts and where they have duplicate WTE. I hope this clarifies it for you. Thank you Ciarán. This is only applicable to calculating the Organisational Headcount, not the Departmental one.
Hi Jing, sorry to bother you again but I have discovered that when trying to calculate the Organisational Headcount and the person has 2 jobs with the same WTE but different contract types i.e. one permanent the other is temporary that the solution adds the Headcount of "1" by default to the temporary post when I need it to default to the permanent post (for those that meet that criteria). I have added another table to demonstrate what I mean. I appreciate that contract type wasn't in my original data but I only realised the significance when double checking the results. I have tried adding the contract type to the grouping and indexing that but again the figures are incorrect. Any assistance would be greatly appreciated and apologies for my error with the original data. For ease I have highlighted in yellow where the previous solution is going wrong.
Hi Jing that worked perfectly, thank you for your time and patience. I have now to calculate the Departmental Headcount and I wonder if you could help me with that. I have attached a screenshot and will upload an example. I thought it might be a variation on what you have already taught me but unfortunately I am struggling again.
Hi Jing unfortunately it does matter as it is counting those people twice in the headcount when it should only be once. For example if the max wte is .5 in both posts, then your method is placing a 1 against both rows when it should be a 1 and then a zero. In excel we use a formula that compares the rows against the rows above and it returns the correct values. I need to replicate the process somehow. Thank you for your assistance.
Hi Jing, I thought you had cracked it and all seemed to be correct then I discovered later that when I worked the figures out manually they differeed. Upon checking I have discovered that your method worked to a point. The only issue that arises is when a person has two WTEs which match. For example a person who has two part time jobs which are 0.50 and .50. Your method records a 1 against each value however we can only count an employee once. Have you any ideas on how to solve that? Sorry for the confusion, in my excitement I thought we had it sorted. I have attached a sample highlighted yellow of where it has gone wrong
This is the query I tried today. I amended your statement from Changed Type to Sorted Rows2 and it runs but took 20 mins to complete. See below.
I have employees that have one post and should be counted once. I have others that have multiple posts that should only be counted once but that should be based solely on the highest WTE.
In excel we data sort by Duplicate NI No by Tax Code by WTE NB WTE must be largest to smallest
We then use an excel formula =if(H2=H1,0,1) in this H represents the column where the tax code is located.
One issue I have noticed is that when I ran your script then it counted those that have two posts but have the same WTE in each post as 1,1. In instances such as that we should only count the person once.
I really appreciate you taking the time to help me.
Hi Pat, it doesn't seem to work as it takes a long time to run (about 20mins) and then only allows me access to 200 rows despite their being 24,000 rows. I copied the text that you provided in your second message and then amended Changed Type to that of my final step. Sorry but would you have any ideas?
#1
mmbr1606
9
Super User 2025 Season 1
#2
stampcoin
7
#3
SD-13050734-0
6
Share
Report
All responses (
Answers (
