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 Source||Side||Qty||Ratio||ComboType||Symbol||Factor||Series Type||MultiExp||Exp Date||MultiStrike||Strike||MultiType||Type||Price||Exchange|
|tlp||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|
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.
What formula do you use in Excel to get the above expected table?
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.
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.