cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adaboul84
New Member

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

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 

 

Add clause.JPG

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1643102509812.png

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.

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1643102509812.png

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
Super User
Super User

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

 

 

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

 

Lookup table.JPG

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

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors