How can I classify strings with a large list of conditionals [Power Query]?

[sbutton]

I'm attempting to use a list of lists / a table on a separate query to categorize / re-label a column of my dataset using values within the dataset. Essentially need a fix to doing a bunch of if-else statements when trying to classify this outside of a Calculated Column with DAX. I already know how to do that, but since this is part of cleaning the data before it's streamed it doesn't really make sense to keep it out of the query. Would anyone know an effective way to do this?

Ex: If [Vendor]=1002 AND [Description].contains("pipe") then [Category] = "Piping, Piping Repairs, " + Description

Since there's hundreds of qualifying statements (vendor + code word) attributed to categories I can't just remedy this with a bunch of if statements. Any help would be greatly appreciated!

[v-eqin-msft · Accepted solution]

Hi @sbutton ,

It depends on what conditions you want to apply.

If your multiple conditions are very different ,like 

1.If [Vendor]=1002 AND [Description].contains("pipe") 

2.If [Vendor]=1003 AND [Description].contains("ABC") 

3.If [Vendor]=1004 AND [Description].contains("DEF") 

......

I think there will be no better way to achieve that, you will need to use many if ...else .

Best Regards,
Eyelyn Qin

[Anonymous]

How many conditions will you have? Have you checked the M of conditional column, then you can edit it in M to for bulky.