Forum Discussion
Conditional column evaluation on grouped data to maintain a distinct list
Bumping and adding new code as I am getting closer but I've again run into the edge of my knowledge.
I am producing a table of distinct wireless numbers and then selecting the appropriate employeeID to return and then relating that back to the AD data to produce a name and other information.
Here's where I am:
{"Wireless Number"},
{
{"Items", each _, type table [Wireless Number=nullable text, GetBaseUserInfo.displayName=nullable text, GetBaseUserInfo.Custom.extensionAttribute15=nullable text, GetBaseUserInfo.UserStatus.user.userAccountControl=nullable number]},
{"Count of Users", each Table.RowCount(_), Int64.Type}
,{"EmpID",
each
if Table.RowCount(_) = 1
then List.Max([GetBaseUserInfo.employeeID])
else if Table.RowCount(_) > 1 then
each if [GetBaseUserInfo.UserStatus.user.userAccountControl] = 512
then List.Max([GetBaseUserInfo.employeeID])
else List.Max([GetBaseUserInfo.employeeID])
else 0, Int64.Type
}
}
I feel like my issue is getting closer to resolve but the step where I am trying to look into the grouped data to find a value seems to produce an output that says "Function". Obviously I am doing something wrong, so perhaps someone can help correct me.
The logic I want is if the rowcount is 1 then give me the empID.
If the rowcount is > 1 then give me the user empID where the status is 512
If the rowcount is > 1 and there are multiple 512 users (this case exists currently due to user error) then give me the user with the higher empID
The else then is if the rowcount is > 1 and there are no 512 users then give me the 514 user with the highest empID
Please help with any insights or tips on how to properly perform this set of nested if statements in this language that I don't fully understand.
Thanks
- lbendlin5 years agoSuper User
You have nested each statements. That can confuse the query engine, and general advice is to use explicit calls instead of the syntax sugar.
- ctaylor5 years agoHelper III
Please...I have stated that I am trying to make something work in a language that I can use in GUI form but once we leave that realm and move to the advanced editor I am lost. Telling me I did something wrong without providing some syntax to correct it seems like trolling at this point.
What do I need to do to be able to evaluate the inner items of the grouped rows?
Again, here is my pseudocode:
The logic I want is if the rowcount is 1 then give me the empID.
If the rowcount is > 1 then give me the user empID where the status is 512
If the rowcount is > 1 and there are multiple 512 users (this case exists currently due to user error) then give me the user with the higher empID
The else then is if the rowcount is > 1 and there are no 512 users then give me the 514 user with the highest empID
Currently the inner evaluations are not happening, hence why i tried another each statement because I simply don't know what I need to do to evaluate the inner items to pick the result I am looking for. That's what I need help with!