Extracting Data from Long String to Create Multiple Columns

[lostinthepatch]

Hi there! 

Trying to create a Power BI report to visualize my day trading. Hopefully being able to see the data in some visualizations will help prevent me from completely destroying my account. 😂 I would also like for it to be as automated as possible hence having a bunch of columns being created from a long string. 

A few examples of the long string are below. There are duplicates because I bought and sold both contracts. 

PUT (BIDU) BAIDU INC SPON ADS JUN 05 20 $105 (100 SHS)
PUT (BIDU) BAIDU INC SPON ADS JUN 05 20 $105 (100 SHS)
CALL (KODK) EASTMAN KODAK CO COM MAY 15 20 $2.5 (100 SHS)
CALL (KODK) EASTMAN KODAK CO COM MAY 15 20 $2.5 (100 SHS)

So this big string has a ton of info that I would like to pull out. I have gotten everything except the Expiration Date (I will explain below)

Here is the general format of this string. Lets use KODK for example from the table and I color coded the important parts

CALL (KODK) EASTMAN KODAK CO COM MAY 15 20 $2.5 (100 SHS) 

CALL - this is the strategy type

KODK - stock ticker

MAY 15 20 - expiration date

$2.5 - strike price

I have been able to get every part pulled out of the above function except for the Date. What I think I need is a way to pull out 10 characters to the left of $ character. I have tried using different combinations of Text.BeforeDelimiter and Text.Range but have not had any luck. Hopefully y'all can help! 

[AlB]

Hi @lostinthepatch 

Hopefully after this you can give us some tips to make money day trading. Copy this in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnb08VHQ8PZ38dZUcHUMDvF19FMA8hy9FZz9gchXwdcxUsHQVMHIQEHFSM9UQcPQwEAh2CNYUyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    #"Added Custom" = Table.AddColumn(Source, "Expiration date", each Date.From(Text.End(Text.Start([Column1],Text.PositionOf([Column1],"$")-1),9)), type date)
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[lostinthepatch]

Haha well the first lesson is to buy low and sell high! 

So here is what I have so far. I tried adding the second piece of code at the bottom (including the comma at the Renamed  Columns2 line). But it returned an error in the column and also didn't run the rest of the code. 

let
    Source = Csv.Document(File.Contents("C:\\history.csv"),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Description", type text}, {"Symbol", type text}, {"Quantity", type number}, {"Price", Currency.Type}, {"Amount", Currency.Type}, {"Commission", Currency.Type}, {"Fees", Currency.Type}, {"Type", type text}, {"Security Description", type text}, {"Settlement Date", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Security Description", "Security Description - Copy"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Duplicated Column", "Text Between Delimiters", each Text.BetweenDelimiters([#"Security Description - Copy"], "$", "("), type text),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Text Between Delimiters", "Strike Price"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Security Description - Copy", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Security Description - Copy", "Options Strategy"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Security Description", "Security Description - Copy"),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Duplicated Column1", "Text Between Delimiters", each Text.BetweenDelimiters([#"Security Description - Copy"], "(", ")"), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Text Between Delimiters", "Ticker"}})
in
    #"Renamed Columns2"

e 

[AlB · Accepted solution]

@lostinthepatch 

Assuming you want the custom column immediately after the code you posted. You need to change Column1 in the last step to the name of the column where you have the text:

let
    Source = Csv.Document(File.Contents("C:\\history.csv"),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Description", type text}, {"Symbol", type text}, {"Quantity", type number}, {"Price", Currency.Type}, {"Amount", Currency.Type}, {"Commission", Currency.Type}, {"Fees", Currency.Type}, {"Type", type text}, {"Security Description", type text}, {"Settlement Date", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Security Description", "Security Description - Copy"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Duplicated Column", "Text Between Delimiters", each Text.BetweenDelimiters([#"Security Description - Copy"], "$", "("), type text),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Text Between Delimiters", "Strike Price"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Security Description - Copy", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Security Description - Copy", "Options Strategy"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Security Description", "Security Description - Copy"),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Duplicated Column1", "Text Between Delimiters", each Text.BetweenDelimiters([#"Security Description - Copy"], "(", ")"), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Text Between Delimiters", "Ticker"}}),

    #"New Custom Col" = Table.AddColumn(#"Renamed Columns2", "Expiration date", each Date.From(Text.End(Text.Start([Column1],Text.PositionOf([Column1],"$")-1),9)), type date)
in
    #"New Custom Col"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[lostinthepatch]

Awesome that worked. 

Is there a way to return a null value if there is not a date? Only options trades will have the date. 

[AlB]

@lostinthepatch 

Give me an example of what a string without the date would look like

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[lostinthepatch]

They typically are just text but occasionally haven numbers

CONOCOPHILLIPS COM

REALITY SHS ETF TR NSD NXGN ECO ETF

ISHARES CORE U.S. AGGREGATE BOND ETF

VANGUARD CHARLOTTE TOTAL INTL BD INDEX

ISHARES CORE U.S. AGGREGATE BOND ETF

VANGUARD CHARLOTTE TOTAL INTL BD INDEX

REALITY SHS ETF TR NSD NXGN ECO ETF

CHEVRON CORP NEW COM

ISHARES CORE U.S. AGGREGATE BOND ETF

VANGUARD CHARLOTTE TOTAL INTL BD INDEX

[AlB]

@lostinthepatch 

If no proper date is found the custom column we created earlier will throw an error. So just catch that with try-otherwise and return null in case of error. This is the updated code for the last step in the last query:

#"New Custom Col" = Table.AddColumn(#"Renamed Columns2", "Expiration date", each try Date.From(Text.End(Text.Start([Column1],Text.PositionOf([Column1],"$")-1),9)) otherwise null, type date)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers