Add Conditional Columns In Power Query at Scale

[Anonymous]

Hi,

I'm looking for a way to add conditional columns in Power Query at scale. I found this article ( https://support.microsoft.com/en-us/office/add-a-conditional-column-power-query-f2422ed1-f565-4e64-b... ) that shows how to manually add a conditional column, but if I have say hundreds of these entries, it would be difficult to manage. Say I had a list with the values in one column and the outputs in the other. Is there a way to create conditional columns based off this list?

Here's an example:

Original Column                                 Column to be Added

Oceansea Ventures                             Ocean

Supersupply Logistics                         Shipper

Conditional Logic:

If A contains B then add C

If Oceansea Ventures contains Sea then add Ocean (non case sensitive)

If Supersupply Logistics contains Logistics add Shipper (non case sensitive)

Ideally, I would have a file that has bindings that Power Query can use. This file would be able to be continually updated. Example:

B > C

Sea > Ocean

Logistics > Shipper

Is there a way to accomplish this? Thanks in advance!

[Anonymous · Accepted solution]

Hi @Anonymous 

It sounds you need a custom function to do SWITCH, put your reference in ref table, then call fnSwitch, file attached for your reference

[v-jingzhang]

Hi @Anonymous 

The solution looks great! I still have many to learn 😆

Best Regards,
Community Support Team _ Jing