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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!