Results 1 to 6 of 6

Thread: load, transform and combine multiple tables

  1. #1
    Neophyte Anonymous1's Avatar
    Join Date
    Jan 2021
    Posts
    3
    Articles
    0
    Excel Version
    Version 2012

    load, transform and combine multiple tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to combine data from tables (formatted as tables) that have identical structures. there are multiple tables per sheet and multiple sheets per workbook and multiple workbooks in a folder. before combining the tables, I need to transform them - each table needs to be transformed the exact same way. provided, I have described the task well, you will notice I am not an expert as the task may sound too simple ... here my steps:
    1. load the files in a query via data / new query / from file / from folder / load
    2. remove columns other than "Content", "Name"
    3. add a "Custom" column with the function Excel.Workbook([Content])
    4. expand that "Custom" column and filter it for "Kind" = 'Table" to get all and only the tables from all sheets in all files
    5. remove columns other than "Content", "Name", "Custom.Name" and "Custom.Data" (the latter now has all the correct tables BEFORE transformation)

    Click image for larger version. 

Name:	tables_loaded_pq.png 
Views:	15 
Size:	39.0 KB 
ID:	10275

    I then don't know how to transform the tables. when I 'combine' the "Content" column, only the first table in a sheet gets transformed (based on the transformation I apply to the "Transform Sample File").
    how can I apply the transformation to all tables in all sheets in all files?

    appreciate any hint or help!

  2. #2
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    86
    Articles
    0
    Excel Version
    Office 365
    No helper can work out solutions and test them with a picture. Or do you expect helpers to create an appropriate data model to be able to help you? That's why I recommend that you create a sample workbook that also contains a manually created desired solution, if possible.

  3. #3
    Neophyte Anonymous1's Avatar
    Join Date
    Jan 2021
    Posts
    3
    Articles
    0
    Excel Version
    Version 2012

    here's my example

    here is the example:
    in combine_query.xlsx I am trying to combine all tables (3 per sheet) from all sheets (2 per file) from all files (2: source_a.xlsx, source_b.xlsx).
    combine_query.xlsx, source_a.xlsx, source_b.xlsx
    before combining, I am trying to extract just one cell from each table. I would like to accomplish this by using a "Transform Sample File", because in the real-world situation, I have a lot more tables/sheets/files.
    in combine_query.xlsx, the result is on tab "combine_query" and the desired outcome on tab "desired_outcome".
    my perception is, I am invoking the Transform File function on the wrong column of binaries ("Content") and I don't know how else I should invoke the Transform File function.
    thanks for looking into this.

  4. #4
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    86
    Articles
    0
    Excel Version
    Office 365
    See here the expected "correct" result with reference of my source.

  5. #5
    Neophyte Anonymous1's Avatar
    Join Date
    Jan 2021
    Posts
    3
    Articles
    0
    Excel Version
    Version 2012
    really appreciate your help, pinarello. I understand you suggest to expand the imported tables (and then filter rows & columns based on cell values) rather than trying to combine them based on a Transform Sample File. while this works perfectly for the example I posted, it will require to modify the query when new tables are added to the folder. would you know of any method allowing for transforming each table based on a function that extracts cell ranges within each table? thanks for your patience with so manny beginner questions.

  6. #6
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    86
    Articles
    0
    Excel Version
    Office 365
    Regardless of the number of workbooks in the folder, this will, without change, always determine from all contained tables of all contained spreadsheets, the row with the user name and output it as a result.

    Apart from the fact that I was convinced that it is so, however, I tested it again by inserting into the folder another workbook with 3 worksheets and a total of 9 tables. After refreshing the query, it then showed the 9 additional rows.

    In this respect, I do not understand why you think that it should not work.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •