Forum Discussion
AdministratorAdding a "1" or a "0"
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
Microsoft EmployeeIn 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
result
Pat
AdministratorHi 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?
- mahoneypat
Can you paste your full query from the Advanced Editor here? Also, since you only have 24k rows, you can wrap the step just before this new step with Table.Buffer to speed things up.
Pat
- Syndicate_Admin
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.