Use a list of tables in Table.Combine

[ChrisDurham]

I have a table with a column of table names in it - over 150. I want to select specific rows from each of these tables and then append them all together into a single table. I know I need to use Table.Combine to append the tables together but how do I first filter the data in each table and then pass that to the Combine function? ie

for each tableName in myTables

     load data from table referenced by tableName

     filter data

     append filtered data to outputTable

loop

If it's easier I may be able to simply append each table in the list together and then filter at the end however there could be several million records in total. 

Can anyone help?

[AlexisOlson · Accepted solution]

Suppose you have queries Query01, Query02, and Query03 loaded into the query editor and a query called "Tables" that lists these in a single column Tables[TableName].

You can use the #shared environment to return a table of these tables as follows:

Record.ToTable(Record.SelectFields(#shared, Tables[TableName]))

You can apply a filter condition to each table. For example, choose rows where ColA is non-null.

= Table.TransformColumns(
      TablesToCombine,
      {{"Value", each Table.SelectRows(_, each [ColA] <> null), type table}}
  )

Once filtered, you can then expand the [Value] column to get your final result.

Here's an example of the full query:

let
    TablesToCombine = Record.ToTable(Record.SelectFields(#shared, Tables[TableName])),
    #"Filter Tables" = Table.TransformColumns(TablesToCombine, {{"Value", each Table.SelectRows(_, each [ColA] <> null), type table}}),
    #"Cols to Expand" = Table.ColumnNames(Query01),
    #"Expanded Value" = Table.ExpandTableColumn(#"Filter Tables", "Value", #"Cols to Expand", #"Cols to Expand")
in
    #"Expanded Value"