load, transform and combine multiple tables

[Anonymous1]

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)



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!

 

[pinarello]

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.

 

[Anonymous1]

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).
View attachment combine_query.xlsx, View attachment source_a.xlsx, View attachment 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.

 

[pinarello]

See here the expected "correct" result with reference of my source.

 

[Anonymous1]

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.

 

[pinarello]

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.