The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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/
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.