Search code examples
excelpowerquerytablename

append tables dynamically

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,

Solution

  • 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 enter image description here

    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. enter image description here