User Profile
lostinthepatch
New Member
Joined 6 years ago
User Widgets
Contributions
Re: Extracting Data from Long String to Create Multiple Columns
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 INDEX2.1KViews0likes1CommentRe: Extracting Data from Long String to Create Multiple Columns
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" e2.1KViews0likes5CommentsExtracting 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!Solved2.1KViews0likes7Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.