Power Query Custom Column

[Anonymous]

Hey,

I want the following:

Type of Contract - number of active employees by type of contract and employee subgroup (column BP and AY), in wich you have to consider:
Contract type "Sem termo (Permanente)" = Open Ended
Contract type "Termo Certo" = Fixed Term
Contract type "Termo Certo" + Employee Subgroup Designation "Estagio Curricular" or "Estagio ExtraCurricu" = Curr. Intern
Contract type "Termo Certo" + Employee Subgroup Designation "Estagio Profissional" = Prof. Intern

Does someone know how to do this with custom column?

[Greg_Deckler]

@Anonymous Seems like a nested if then else statement so:

if <condition> then <if true> else if <condition> then <if true> else if ...

[Anonymous]

if [Termo do Contrato] = "Sem termo (Permantente)" then "Open Ended"

else if [Termo do Contrato] = "Termo Certo" then "Fixed Term"

else if [Termo do Contrato] = "Termo Certo" and [Dsg Sub Grupo Empr] = "Estagio Curricular" or [Dsg Sub Grupo Empr] = "Estagio ExtraCurricu" then "Curr. Intern"

else if [Termo do Contrato] = "Termo Certo" and [Dsg Sub Grupo Empr] = "Estagio Profissional" then "Prof. Intern"

else "0"

This gives me only '0'

[v-jingzhang]

Hi @Anonymous 

If it only gives you "0", it means all of the above conditions are not met. The [Termo do Contrato] column is of Text type, right? Check if there is any typo in the values. Maybe there is some whitespace before or after the values, which is invisible and easy to be ignored. Under Transform tab, select Format > Trim to remove the leading and trailing whitespaces.

And in the third condition, please use parentheses to wrap [Dsg Sub Grupo Empr] = "Estagio Curricular" or [Dsg Sub Grupo Empr] = "Estagio ExtraCurricu" part as AND has a higher priority over OR. You need to add parentheses to ensure the calculation priority. 

If this doesn't help, please share some dummy data or pbix which can reproduce this problem, as well as your expected result. With them, we can help look for the detailed cause. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.