Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

ISSUE with Switch

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.

General Comment
Show and Tell
  • edhans's avatar
    edhans
    6 years ago

    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

      

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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