Extracting Data from Long String to Create Multiple Columns

Anonymous 

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