Power Query - Multiple OR statement with values

2
votes

I've been doing research on this and I find a plethora of articles related to Text, but they don't seem to be working for me.

To be clear this formula works, I'm just looking to make it more efficient. My formula looks like: if [organization_id] = 1 or [organization_id] = 2 or [organization_id] = 3 then "North" else if … where organization_id is of type "WholeNumber"

I'd like to simplify this by doing something like: if [organization_id] in {1, 2, 3} then "North" else if …

I've tried wrapping in Parenthesis, Braces, & Brackets. Nothing seems to work. Most articles are using some form of text.replace function and mine is just a custom column.

Does MCode within Power Query have any efficiencies like this or do I have to write out each individual statement like the first line?

1
powerqueryexcel-2016m

1 Answers

2
votes

I've had success with the a List.Contains formulation:

List.Contains({1,2,3}, [organization_id])

The above checks if [organization_id] is in the list supplied in the first argument.

In some cases, you may not want to hardcode a list as shown above but reference a table column instead. For example,

List.Contains(TableWithDesiredIds[id_column], [organization_id])