Update multiple columns based on data in description column

Question

Hi,I need to be able to categorize the data in the description column when the data is imported. &nbsp;When done, every row of data would have these a Lead Schedule Code and Transaction Type updated based on the criteria shown. &nbsp;&nbsp;The source data contains the info in the description column. &nbsp; &nbsp;I need to populate the Lead Schedule Code column and Transaction Type column to the values shown in the sample data shown below. &nbsp;Any new descriptions that are not already updated would still be blank. &nbsp; Then I need to add another line of code to properly categorize the new item..&nbsp;The criteria based on the description column is used to update two (or more fields) with the Lead Schedule Code and Transaction Type indicated.&nbsp;How can this be done in Power Bi?ThanksJohnDescription &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Criteria &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Lead Schedule code &nbsp; &nbsp; Transaction type&nbsp;Bought 1 AAPL Apr 13 2018 167.5 Put @ 1.38&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Description starts with "Bought" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trade &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BuyBought 1 AAPL Apr 13 2018 170.0 Put @ 2.08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Description starts with "Bought" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trade &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BuyINCOMING ACCOUNT TRANSFER &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description equals "ACCOUNT TRANSFER" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Transfer &nbsp; &nbsp; &nbsp; Transfer InORDINARY DIVIDEND (SPY) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description starts with "ORDINARY DIVIDEND"&nbsp;&nbsp; Income &nbsp; &nbsp; &nbsp; &nbsp;DividendSold 1 AAPL Apr 13 2018 172.5 Put @ 3.25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description starts with "Sold" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trade &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SellREMOVAL OF OPTION DUE TO EXPIRATION &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description contains "EXPIRATION" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trade &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sell

ashish_mathur · Accepted Answer

Hi,
&nbsp;
Write this calculated column formula to get the desired Lead schedule Code result
&nbsp;
=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())))
&nbsp;
Based on this please try yourself to get the result of Transaction type.
&nbsp;
Hope this helps.
&nbsp;

Forum Discussion

Update multiple columns based on data in description column

1 Reply

Related content

Column Description

Sequential Order column based on multiple criteria

Retrieve column descriptions from semantic models

Multiple line and clustered columns chart

Bank Statement, Description Across Multiple Rows