Forum Discussion

stockturner's avatar
stockturner
Regular Visitor
8 years ago
Solved

Multiple table formats in a single text source file

Hi,

I'm new to Power Bi, although I've worked extensively with excel, and developed some MS Access, non VBA solutions.    The data file I get from my broker contains multiple tables of data within the same text file that is being exported.   If I delete rows so that only 1 section remains (Account Trade History for example), then the data imports cleanly with the headings shown, using the comma delimited info.   However, I don't want to open the text file, and then break it down into 9 different files before importing.    Overall this data includes tables that have different structures, that represent account activity.  

 

Any suggestions would be appreciated.  Further details below.

 

Thanks

John (stockturner)

 

The tables below are contained within a single text file.   I want to capture the info in separate tables and use it as described.

 

  1. Account number / time frame - I want to capture this info.  Then I want to be able to put the account number on each row of the actual data below so I can use this process for multiple brokerage accounts.    Example, IRA account, taxable account, spouses IRA account etc.  
  2. Cash Balance - This section contains the cash transactions.   Key field in this section is the Ref # which is the same as the Order ID in the Account Trade History.  Need to set up a relationship between these two.
  3. Futures statement - don't care about this data now.
  4. Forex Statement - dont care about this data now.
  5. Total Cash - I want to keep this data.  Will add the account number and date from the Account statement row.
  6. Account Order History - not critical.  Has filled and cancelled orders.   I would capture this because it contains some notes, but to use the info, I would need to create a key field to relate it to the other data. 
  7. Account Trade History - Need this data.  This is the actual trade that happened.   Need the account number to go with each record in this section.
  8. Profits and losses - useful info but could recreate from data.
  9. Account summary info.   I want these lines of data as part of my control totals for this account on this date.

 

Screenshot below contains a excerpt of the data.

 

 

Sample data from CSV file

Need Help
  • stretcharm's avatar
    stretcharm
    8 years ago

    Here is a simple example of splitting types

     

    Load the file ensure the widest no columns are imported

    Basically add a conditional column to identify the first row of each type.

    use fill down to set all the rows between types to the header type.

    Then create references for each type and filter by the type column,

    From here you can shape each type of date

     

    Here is the advance editor code.

    let
    Source = Csv.Document(File.Contents("L:\Downloads\Sample_data_multple_types.csv"),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Added Conditional Column" = Table.AddColumn(Source, "RowType", each if [Column1] = "Row type 1" then "Type1" else if [Column1] = "Row type 2" then "Type2" else if [Column1] = "Row type 3" then "Type3" else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"RowType"})
in
    #"Filled Down"

     

    Type 1 code

     

    let
    Source = Sample_data_multple_types,
    #"Filtered Rows" = Table.SelectRows(Source, each ([RowType] = "Type1")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"})
in
    #"Removed Columns"

1 Reply

  • stretcharm's avatar
    stretcharm
    Memorable Member
    8 years ago

    Here is a simple example of splitting types

     

    Load the file ensure the widest no columns are imported

    Basically add a conditional column to identify the first row of each type.

    use fill down to set all the rows between types to the header type.

    Then create references for each type and filter by the type column,

    From here you can shape each type of date

     

    Here is the advance editor code.

    let
    Source = Csv.Document(File.Contents("L:\Downloads\Sample_data_multple_types.csv"),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Added Conditional Column" = Table.AddColumn(Source, "RowType", each if [Column1] = "Row type 1" then "Type1" else if [Column1] = "Row type 2" then "Type2" else if [Column1] = "Row type 3" then "Type3" else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"RowType"})
in
    #"Filled Down"

     

    Type 1 code

     

    let
    Source = Sample_data_multple_types,
    #"Filtered Rows" = Table.SelectRows(Source, each ([RowType] = "Type1")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"})
in
    #"Removed Columns"