Passing a Dynamic List of Values into Table.Combine

Question

I have a relatively simple issue, which seems as if it should be achievable, but I have tried absolutely everything with no success.

Here is the situation:

Using Power Query inside Excel I would like to be able to combine multiple queries (lets call these: Query1, Query2 and Query3) into one single query using the Table.Combine function.

The only catch is that the list of queries I will be combining will be dynamic and dependant on another query (lets call this: QueryList)

For example, under certain circumstances QueryList will be:

• Query1
• Query2
• Query3

and under some other condtions QueryList may simply be:

• Query1
• Query3

What I would like to do is to be able to parse the value of QueryList into the Table.Combine Function:

eg. Table.Combine(#"QueryList")

and thereby allow dynamic consolidation of queries

Whats happening is that I am getting an error that states:

Expression.Error: We cannot convert the value "Query1" to type Table. Details: Value=Query1 Type=Type

Update: I have tried variations of Table.ToList, using { } to create a list, TableFromlist, all with no success (normally errors complain about not being able to comvert from text to list or to table etc.

Thanks in advance for the help.

Answer 1

If your QueryList would be {Query1, Query2} then Table.Combine(QueryList) would work.

Apparently, your QueryList is {"Query1", "Query2"}.

So the strings must be converted to tables, which can be done using Expression.Evaluate. As second parameter, you must supply a record with all possible queries, so the formula becomes, for Query1, Query2, Query3:

= Table.Combine(List.Transform(QueryList, each Expression.Evaluate(_, [Query1 = Query1, Query2 = Query2, Query3 = Query3])))