Forum Discussion
Adding a "1" or a "0"
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.
I separate the transformation steps. Not sure if this would improve the performance. You could have a try.
1. First click on Group By under Transform tab. Switch to Advanced section. Group on Employee column and perform the following two operations to add two new group columns:
Max WTE: Max on WTE column;
All Data: All Rows
2. Expand the All Data column.
3. Add a conditional column: Add column tab > Conditional column.
4. Remove unnecessary columns and resort columns.
Result:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.I think of another method which can meet your need. Instead of comparing every WTE with the maximum WTE, I sort the grouped tables descendingly by WTE, then add an Index column starting from 0 in every grouped table. Then mark rows where index value is 0 as 1 in Head Count column. Open Query 2 in the new attached sample file to see detailed steps.
Result
Best Regards,
JingSorry for the late reply. The operation needs to be modified a little bit. When grouping by, switch to Advanced option and group by Employee and Dept columns at the same time. See below image.
Then add an Index column to every grouped table, starting from 1 with increment 1. Mark every row with index 1 as 1 and mark other rows as 0. See Query 1 in the attachment.
Best regards,
Jing
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
You just need to modify the Group step in my last sample file. Group by only Employee column rather than Employee&Dept. Keep the other steps same. See the attached file.
Jing
22 Replies
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUMzAAUn75QMLR0RFI+hYVK4BoQ6VYnWglIyDLQM/MHEhFphYDSScnJ6giJzRFxsY4FBmAFRmjm+Ts7AxWpOAMNwiixsgAuxqIOSaE1MQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t, WTE = _t, #"Duplicate NI No" = _t, #"Tax Code" = _t, Employee = _t, #"Trust H/C" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"WTE", type number}, {"Duplicate NI No", type text}, {"Tax Code", type text}, {"Employee", type text}, {"Trust H/C", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let emp = [Employee], wte = [WTE], maxwte = List.Max(Table.SelectRows(#"Changed Type", each [Employee] = emp)[WTE]), result = if wte = maxwte then 1 else 0 in result, Int64.Type) in #"Added Custom"Pat
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
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.
In your query, on the Add Column tab, add a custom column and put the expression below in the pop-up box. If needed, update the column references for Employee and WTE. Also, if #"Changed Type" is not the name of your last step, update that with the name of the last step.
let emp = [Employee], wte = [WTE], maxwte = List.Max(Table.SelectRows(#"Changed Type", each [Employee] = emp)[WTE]), result = if wte = maxwte then 1 else 0 in resultPat
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?
I separate the transformation steps. Not sure if this would improve the performance. You could have a try.
1. First click on Group By under Transform tab. Switch to Advanced section. Group on Employee column and perform the following two operations to add two new group columns:
Max WTE: Max on WTE column;
All Data: All Rows
2. Expand the All Data column.
3. Add a conditional column: Add column tab > Conditional column.
4. Remove unnecessary columns and resort columns.
Result:
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Thank you so much, this has resolved the formula and performance issues. This is just fantastic and it will save me so much time.
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
It doesn't matter. I understand the scenario. My question is that when a person has multiple WTEs that match his maximum WTE, do you have any priority rule pointing that one specific WTE row must be marked as 1? Or you don't mind that so any one is ok to be marked as 1?
Jing
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.
Sorry for the late reply. The operation needs to be modified a little bit. When grouping by, switch to Advanced option and group by Employee and Dept columns at the same time. See below image.
Then add an Index column to every grouped table, starting from 1 with increment 1. Mark every row with index 1 as 1 and mark other rows as 0. See Query 1 in the attachment.
Best regards,
Jing
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
