Power Query - Multiple OR statement with values

Question

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?

Alexis Olson · Accepted Answer · 2020-05-04 21:32:11Z

3

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])

answered May 4, 2020 at 21:32

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MartyMcfly0033 Over a year ago

Thanks Alexis! That works. Unfortunately I don't already have a list made so I'll just combine them statically like this for the time being. But it is noted for future use!

Collectives™ on Stack Overflow

Power Query - Multiple OR statement with values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related