Update multiple columns based on data in description column

[stockturner]

Hi,

I need to be able to categorize the data in the description column when the data is imported.  When done, every row of data would have these a Lead Schedule Code and Transaction Type updated based on the criteria shown.  

The source data contains the info in the description column.    I need to populate the Lead Schedule Code column and Transaction Type column to the values shown in the sample data shown below.  Any new descriptions that are not already updated would still be blank.   Then I need to add another line of code to properly categorize the new item..

The criteria based on the description column is used to update two (or more fields) with the Lead Schedule Code and Transaction Type indicated.

How can this be done in Power Bi?

Thanks

John

Description                                                                 Criteria                                            Lead Schedule code     Transaction type

Bought 1 AAPL Apr 13 2018 167.5 Put @ 1.38        Description starts with "Bought"                         Trade            Buy

Bought 1 AAPL Apr 13 2018 170.0 Put @ 2.08        Description starts with "Bought"                         Trade            Buy

INCOMING ACCOUNT TRANSFER                          Description equals "ACCOUNT TRANSFER"          Transfer       Transfer In

ORDINARY DIVIDEND (SPY)                                    Description starts with "ORDINARY DIVIDEND"   Income        Dividend

Sold 1 AAPL Apr 13 2018 172.5 Put @ 3.25            Description starts with "Sold"                               Trade           Sell

REMOVAL OF OPTION DUE TO EXPIRATION          Description contains "EXPIRATION"                     Trade           Sell

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula to get the desired Lead schedule Code result

=if(ISNUMBER(SEARCH("Bought",[Description],,BLANK()))||ISNUMBER(SEARCH("Sold",[Description],,BLANK()))||ISNUMBER(SEARCH("Expiration",[Description],,BLANK())),"Trade",IF(ISNUMBER(SEARCH("Account Transfer",[Description],,BLANK())),"Transfer",if(ISNUMBER(SEARCH("Ordinary Dividend",[Description],,BLANK())),"Income",BLANK())))

Based on this please try yourself to get the result of Transaction type.

Hope this helps.