Forum Discussion
Im triying to create a dynamic Append quarry
The drill-down doesn't work because it's actually drill-down on the text values instead of the objects, however there is no need to drill-down and create a separate list. Combining the column of tables will be dynamic without this step. It is also unnecessary to have each table as a separate query, unless there are individual transformations that must be applied to each one before they can be combined (there are other ways to handle this too but they are very complex).
If you are simply trying to combine all tables listed in the Main query dynamically, you can do so like what's listed below. However, if you are trying to combine multiple Excel files, there are extra steps that need to be taken to avoid challenges such as headers being repeated in the combined table. Power Query provides you with a feature which handles this for you.
Click the double-arrows button in the Content column header and Power Query will guide you through the process. This creates a custom function (and invokes the resulting function for you) which lets you define some transformations that should be applied to all files before combining the content, such as promoting the first row to headers.
This is a good detailed explanation.
https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/
Alternate (and info that's good to know when you want to combine other kinds of tables).
Notes - Add a new column to convert the binary to Excel. I have named this new column Data. Since it is possible for different tables to have different columns/attributes, a best practice is to include a statement which creates a list of all columns in all the listed tables tables before combining the tables (if it is certian that this will not be different, like getting the structure of a list of xlsx files, this can be skipped).
BinaryToExcel = Table.AddColumn(Main, "Data", each Excel.Workbook ( [Content] )), GetFileObjects = Table.ExpandTableColumn(BinaryToExcel, "Data", Table.ColumnNames(Table.Combine(BinaryToExcel[Data])))This will add a new set of columns to the existing rows which contains the objects from each respective file, from which, you will need to filter for the appropriate file objects, like sheets with specific names, etc. and then combine and expand again.
Table.ExpandTableColumn(GetFileObjects , "Data", Table.ColumnNames(Table.Combine(GetFileObjects [Data])))As you can see, the data from the sheets/tables has been combined with one hiccup - the header row from each is present in multiple rows of the table. You will need to now filter those out.
I get my list by pressing "Drill down" on my "main" table. same surce that I create my tables from.
for example my ASOSUK source is the same .
my original sorce is a folder that we same customer files to each week.
how can I create a dynamic list? I want to be able to combine my file undependent on which customer have shared thier file in a specif week.
Super UserThe drill-down doesn't work because it's actually drill-down on the text values instead of the objects, however there is no need to drill-down and create a separate list. Combining the column of tables will be dynamic without this step. It is also unnecessary to have each table as a separate query, unless there are individual transformations that must be applied to each one before they can be combined (there are other ways to handle this too but they are very complex).
If you are simply trying to combine all tables listed in the Main query dynamically, you can do so like what's listed below. However, if you are trying to combine multiple Excel files, there are extra steps that need to be taken to avoid challenges such as headers being repeated in the combined table. Power Query provides you with a feature which handles this for you.
Click the double-arrows button in the Content column header and Power Query will guide you through the process. This creates a custom function (and invokes the resulting function for you) which lets you define some transformations that should be applied to all files before combining the content, such as promoting the first row to headers.
This is a good detailed explanation.
https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/
Alternate (and info that's good to know when you want to combine other kinds of tables).
Notes - Add a new column to convert the binary to Excel. I have named this new column Data. Since it is possible for different tables to have different columns/attributes, a best practice is to include a statement which creates a list of all columns in all the listed tables tables before combining the tables (if it is certian that this will not be different, like getting the structure of a list of xlsx files, this can be skipped).
BinaryToExcel = Table.AddColumn(Main, "Data", each Excel.Workbook ( [Content] )),
GetFileObjects = Table.ExpandTableColumn(BinaryToExcel, "Data", Table.ColumnNames(Table.Combine(BinaryToExcel[Data])))
This will add a new set of columns to the existing rows which contains the objects from each respective file, from which, you will need to filter for the appropriate file objects, like sheets with specific names, etc. and then combine and expand again.
Table.ExpandTableColumn(GetFileObjects , "Data", Table.ColumnNames(Table.Combine(GetFileObjects [Data])))
As you can see, the data from the sheets/tables has been combined with one hiccup - the header row from each is present in multiple rows of the table. You will need to now filter those out.