Answered

Adding a "1" or a "0"

(0) Share

Report

Posted on by Ciaran

263

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.

Sample Data.xlsx

Categories:

Power Query

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

Hi Pat, apologies I am very much a newbie but when I copy the text in as you outlined I am getting an error return

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

I have managed to get the error to clear however it is displaying the sample data as the source, can you advise how to change it to the actual source, thank you for your help.

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

Thank you so much, this has resolved the formula and performance issues. This is just fantastic and it will save me so much time.

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

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.

Dept Example Data...

Was this reply helpful? Yes No
Verified answer

Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

Hi Jing, thank you so much for all your assistance, This has helped me immensely and I really appreciate it as the time saved is enormous. Your patience and explanations were fantastic. Take care - Ciaran

Was this reply helpful? Yes No
Ciaran 263 on at

Like (0)

Report
Copy link

Link copied!

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.

Sample Data for O...

Was this reply helpful? Yes No