none
Splitting multiple tables stacked in one csv file. RRS feed

  • Question

  • I use Excel for engineering. Many applications that I use export data with multiple unrelated tables in a single csv file. eg. 

    #TBL1

    HEADING1.1, HEADING 1.2, HEADING 1.3

    #TBL2

    HEADING 2.1, HEADING 2.2

     I want to split these files into separate tables using power query. I know I could split the tables into separate CSV files using VBA but I would prefer to do all of the data transformation in the one application so that I don't have to remember to run my data through a vba script first.  

    What i have done so far is

    1. add a column with the table header and fill down so that all of the rows I want to split can be identified. 

    2. i have then grouped rows by the column that i added

    I can access each table now by clicking on the table in the memberTables column. 

    Do I need to create a copy of the query for each table that I want to work with? or is there a way to automatically split the query into the multiple tables?

    I have >50 tables in each csv file so i would prefer not to copy them.....

    Monday, August 10, 2020 4:55 AM

All replies

  • Hi Joe,

    one query can only return one table. 
    Also, there is no way to trigger the creation of other queries from one query.



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, August 25, 2020 11:13 AM
    Moderator
  • Hi Joe. Instead of duplicating your logic, you can reference the query that breaks them up. Create N queries, each of which references the main query but retrieves only a single table from the list.

    Ehren

    Tuesday, August 25, 2020 6:58 PM
    Owner