append tables dynamically

Question

Is there a way to create/append a dataset dynamically with all tables in an excel file with powerquery ? I guess I need a function to append all the tables in the below list.

all tables have the same column names.

#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
Name = #"Filtered Rows"[Name]

table names as example

Table1
Table12
Table143
Table14354

thanks,

Answer 1

In terms of doing it in the current file you could use the following, and on the assumption that all the tables have the same column names you could try the following

let
    Source = Excel.CurrentWorkbook(),
    Custom1 = Table.SelectRows(Source, each try List.Contains(Table.ColumnNames(_[Content]),"Col1") otherwise false)
in
    Custom1

in this example I've got a bunch of tables, all with the same headers, a print range and filtered range in the file

The Source step, ie Excel.CurrentWorkbook(), brings in the following

The 2nd step checks the [Content] column to see if the column names includes Col1 (obviously you'd need to change this for a header value from your tables), with some error handling built in.

Answer 2

i found the answer... after filtering the Tables, we need to combine them..

though i still couldnt manage it in the same file...

Source = Excel.Workbook(File.Contents("C:\Users\777\filename.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
CombineTable= Table.Combine(#"Filtered Rows"[Data])