Im triying to create a dynamic Append quarry

[Mpoweruser]

HI, 

Im uploading severeal excel sheet from a folder (that containes data from diffrent customers). this folder will be updated each week but not I will not always have same file. So I want to create a dynamic Append quarry. 
this is the line for the "source"
= Table.Combine({#"ASOS UK", #"ASOS US", Fenwick, Harrods, HOF, JLP, Jarrold, #"Space NK", voisins, #"HNIX (2)"})

I created a list with the same names and tried to call to my list instead 
= Table.Combine(#"Main (2)")
but get below error
Expression.Error: We cannot convert the value "ASOS UK" to type Table.
Details:
Value=ASOS UK
Type=[Type]

can someone please help me.
thank you

[peterpan]

let
    Source = Folder.Files("C:\Users\rscsh\Desktop\OATesting\ABC\ToSign\Post Sign\Successful"),
    #"Added Custom" = Table.AddColumn(Source, "SheetTable", each Excel.Workbook([Content],  true))    
in
    #"Added Custom"

Try using this and expand the SheetTable column basis the sheets you want. Now use Table.Combine(TableName[Data]) and it'll combine it all

[jennratten]

Hello - The function Table.Combine has an input of a list of tables.  If you have two tables (Table1 and Table2) you can create a list of tables by wrapping them in curly braces.  The tables within this list can be appended to each other by placing it inside Table.Combine.  The error you are getting says that one of the items in your list of tables is text instead of a table.  It looks like your second refrence for the table ASOS UK is just missing the number symbol.  Please try adding that. Without it, it is simply reading in a text value of ASOS UK instead of the table variable.

[Mpoweruser]

as you can see below I did adjust the name on my list to match my tables 

when I replace the list in my Table.Combine from 

= Table.Combine({ASOSUK, ASOSUS, Fenwick, Harrods, HOF, JLP, Jarrold, SpaceNK, voisins, HNIXfinal})
to

= Table.Combine(#"Main (2)") (im calling my list)
I still get 
Expression.Error: We cannot convert the value "ASOSUK" to type Table.
Details:
Value=ASOSUK
Type=[Type]

[jennratten]

I see - it is not working for you because your list is a list of text values instead of a list of tables.  How did you create your list? If you manually typed it, then you will need to refer to each table object as a table to create your list, like so:

TableList = { ASOSUK, ASOUS, Fenwick, HARRODS, HOF, JarroId, JLP, SpaceNK, Viosins }

Then you can combine it like this:

Append = Table.Combine ( TableList )

I see in your snip that you are replacing some of the text in what seems to be a table name.  That will only work on text.  If you are using some logic to conditionally retrieve the list of tables then please share that logic so I can further assist.

[Mpoweruser]

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.

[jennratten · Accepted solution]

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.