ISSUE with Switch

[Anonymous]

Hi 

I am trying to select multiple text criteria from a column in power query. I thought switch would work but this does not work, but works outside of it. 

The data is as follows 

Farm

Farmer

Grower

Producer 

Manager 

Director 

owner 

Im trying to choose Farm, Grower, manager, director and flag these with a one in a new column

I have tried the below:

SWITCH(
([Job role.Job Role]),

"Director",1,
"Grower",1,
"Manager",1,
0)

But this dont work and I get the below error:

Expression.Error: The name 'SWITCH' wasn't recognized. Make sure it's spelled correctly.

Please help with either the formula or suggest an alternative as i have 20 selections to make in my report, multiple times.

[v-eachen-msft]

Hi @Anonymous ,

If you want some codes like SWITCH(), you could refer to this blog.

https://blog.crossjoin.co.uk/2014/03/10/conditional-logic-in-power-query/

[edhans · Accepted solution]

There is no SWITCH() function in Power Query. All Power Query functions have the type and function name with a period, so Text.Start(), Number.From(), DateTime.Date(), etc. 

SWITCH is DAX only. In Power Query, you'd need to use nested if/then/else statements, or use a custom function that does SWITCH functionality, but I would advise against that unless you are pretty good in Power Query.

If you can share what you are tryting to do, we can assist getting you started with if/then/else.

EDIT: Saw your sample logic. Try something like this:

= if [Job Role] = "Director" then 1
else if [Job Role] = "Grower" then 1
else if [Job Role] = "Manager" then 1
else null

or this if you have a default you want to use beside a null value.

if [Job Role] = "Director" then 1
else if [Job Role] = "Grower" then 1
else if [Job Role] = "Manager" then 1
else 2