[SOLVED] How to streamline else if statements in Power Query M for a new Add Conditional Column

How to streamline else if statements in Power Query M for a new Add Conditional Column

I created a new Conditional Column named [CATEGORY] based on many different values in a column named [MERCHANT]. I'm categorizing the values in the MERCHANT columns. Many of the values in MERCHANT will change each month.

Currently, my Conditional Column has many if else statements and I'm wondering how I could streamline it. It works but it is very very long.

Here is a small sample of my statement:

 #"Added Conditional Column" = 
      Table.AddColumn(#"Sorted Rows1", "Category", each 
         if Text.Contains([Merchant], "Farmer's Market") then "Grocery" 
         else if Text.Contains([Merchant], "CFSD") then "School" 
         else if Text.Contains([Merchant], "76") then "Gas" 
         else if Text.Contains([Merchant], "Wildflower") then "Grocery" 
         else if Text.Contains([Merchant], "Chvrn") then "Gas" 
         else "Misc"

Thank you!

Solution

Instead of using a long string of IF statements, consider adding a table with Merchant and Category. This table needs to be kept up to date manually to cover your most frequently used merchants.

Now you can perform a merge in Power Query on the Merchant column in both tables and expand the category column. Anything row not matched will have a blank category, which you can then associate with "misc".