Forum Discussion
Extracting Data from Long String to Create Multiple Columns
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!
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
7 Replies
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
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
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
