Is there a way to import multiple clauses/conditions to add new conditional column in power query

[adaboul84]

Hi, Is there a way to import multiple conditions into Power Query  "add new conditional column". I am adding new column based on set of multiple conditions "all of them are the "contains" condition" , however, my list of conditions is too long (>50) and keeps increasing, so I don't want to add conditions manually, appreciate if you areaware about an auto solution to link the conditions another query "lookup table".

Example: as you see below, I have monthly bank statements that contains almost the same transaction descriptions every month. I want power query to add new column "expense account" based on the description wording (e.g. if the transaction description contains "FACEBK", then the expense account should give "Advertising expense") something similar to bank rules in most accounting software (QBO/XERO). 

as you see I have to add 50 clauses manually, I am looking for an option to import these 50 clauses (conditions) one time.

Thanks 

[v-angzheng-msft · Accepted solution]

Hi, @adaboul84 

I encapsulated a function that replaces the value, which can be invoked on the column. This works for all columns.

Create a blank query, open Advanced Editor and replace the text there with the code below.  In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your "Old" column as the input.

(inputtext as text) =>
  let
    LookupValue = Table.SelectRows(LookupTable,(y)=>Text.Contains(inputtext,y[Contain]))[New],
    Result=if List.Count(LookupValue)=0 then inputtext else LookupValue{0}
  in
    Result

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

From Table1, merge with the LookupTable matching on Description, then expand the lookup column.

[adaboul84]

Hi Alexis, thank for your reply, please see this screen shot of the lookup table. the description column in the bank statement and the lookup table "condition column" are not identical, even when I tried fuzzy match the result was not so good.

that's why I abandoned the merge queries option and focused on adding conditional column, because conditional columns with the operator selected to be "contains" gives accurate "expense accounts", but my pain it is time consuming to manually add clauses"conditions

[AlexisOlson]

See if my answer to this post helps:

https://community.powerbi.com/t5/Power-Query/If-Text-Contains-string-substringsA-as-list-insert-subs...

Translating this to your column names:

(row) =>
List.Max(
    Table.SelectRows(
         LookupTable,
         each Text.Contains(row[Description], [Condition])
    )[Expense Account]
)