Showing results for 
Search instead for 
Did you mean: 
stockturner Frequent Visitor
Frequent Visitor

Copy selected text from description column and place into separate columns

Hi PowerBi Team,


I'm new to Power Bi and going through my first project which relates to stock and options trading data from the broker.    I need some help to place certain elements of the description field into separate fields so I can do further work.


Sample data in description

tIP BOT +2 AMZN @1530.15
BOT +5 DIS @101.26
AUTO_Btrends BOT +4 AAPL 100 (Weeklys) 9 MAR 18 172.5 CALL @6.60
tIP SOLD -14 V 100 (Weeklys) 23 MAR 18 125 CALL @1.00 PHLX
tIP BOT +2 1/2 BACKRATIO V 100 (Weeklys) 23 MAR 18 124/123 PUT @.26 ISE
tIP SOLD -6 1/-2/-1/2 CUSTOM DIS 100 18 MAY 18/18 MAY 18/13 APR 18/13 APR 18 100/95/104/103 PUT/PUT/PUT/PUT @2.34 CBOE



Sample Output that I need

Order SourceSideQtyRatioComboTypeSymbolFactorSeries TypeMultiExpExp DateMultiStrikeStrikeMultiTypeTypePriceExchange
tlPBOT2  AMZN        1530.16 
 BOT5  DIS        101.26 
Auto_BtrendsBOT4  AAPL100Weeklys 3/9/2018 172.5 Call6.6 
tlpSOLD-14  V100Weeklys 3/23/2018 125 Call1PHLX
tlPBOT21/2Back RatioV100Weeklys 3/23/2018124/123  Put0.26ISE
tlpSOLD-61/-2/-1/2CustomDIS100 18 MAY 18/18 MAY 18/13 APR 18/13 APR 18 100/95/104/103 PUT/PUT/PUT/PUT 2.34CBOE


As you can see, not every sample output field is available in every row of source data.   I've done this before in excel, by filtering on the description, and then writing formulas for those particular layouts,  I keep filtering until all of the applicable columns have been broken out.  I keep the original field for some reporting and as a control, but need to do other steps using the info presented.


I'm at a loss at how to do this in PowerBi.    Any suggestions appreciated.







Moderator v-yuezhe-msft

Re: Copy selected text from description column and place into separate columns


What formula do you use in Excel to get the above expected table?


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
stockturner Frequent Visitor
Frequent Visitor

Re: Copy selected text from description column and place into separate columns

In the example I posted, the columns were populated manually for the purpose of illustrating the desired output.   


When I actually do this in excel with hundreds of rows, I would go through a lengthy process to develop a solution the first time.    I create a series of helper columns with string functions and if statements to break up the data in a logical place to capture the fields.  I also would put certain known items in a separate table and use Vlookup to make the maintenance easier.     


Essentially I'd sort the description so like items were grouped together, and strip off logical pieces. Then I'd evaluate what was left and repeat the process.  


For example,


If the first word is "tlP or "Auto_Btrends" I know from the data this belongs in the Order Source field. If it is not either of those two words, I know it is a default order type.


I'd then copy the description to a temp description 1 field, excluding the first word, and re sort.


In this second sort, the first word would either be bot (meaning bought) or sold That would provide the data for the Side column. The Side data would be stripped off and the remaining phrase placed in a temp description 2 field.


That would leave the quantity. I know that is valid because it is an integer with a + in front of it for bought and a - for sold.   This would provide the data for the quantity column.   Clone the remaining description to a temp description 3 field, re-sort and continue.


I might then strip off data from the right side.   The exchanges could be ISE, PHLX, or others, and this would give me the data for the exchange field.


Then I'd capture the price after the @ sign.


Eventually I would get all of the pieces in the right columns, but it is not easy.


I recently found a cleaner data source so I am working on a different approach to the problem.