Combine Tables matching a name pattern in Power Query

Question

I am trying to combine many tables that has a name that matches a patterns. So far, I have extracted the table names from #shared and have the table names in a list.

What I haven't being able to do is to loop this list and transform in a table list that can be combined.

e.g. Name is the list with the table names:

Source = Table.Combine( { List.Transform(Name, each #shared[_] )} )

The error is:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=[List]
    Type=[Type]

I have tried many ways but I am missing some kind of type transformation.

I was able to transform this list of tables names to a list of tables with:

T1 = List.Transform(Name, each Expression.Evaluate(_, #shared))

However, the Expression.Evaluate feels like an ugly hack. Is there a better way for this transformation?

With this list of tables, I tried to combine them with:

Source = Table.Combine(T1)

But I got the error:

Expression.Error: A cyclic reference was encountered during evaluation.

If I extract the table from the list with the index (e.g T1{2}) it works. So in this line of thinking, I would need some kind o loop to append.

Steps illustrating the problem.

1. The objective is to append (Tables.Combine) every table named T_\d+_Mov:

2. After filtering the matching table names in a table:

3. Converted to a List:

4. Converted the names in the list to the real tables:

5. Now I just need to combine them, and this is where I am stuck.

It is important to not that I don't want to use VBA for this. It is easier to recreate the query from VBA scanning the ThisWorkbook.Queries() but it would not be a clean reload when adding removing tables.

The final solution as suggested by @Michal Palko was:

CT1 = Table.FromList(T1, Splitter.SplitByNothing(), {"Name"}, null, ExtraValues.Ignore),
EC1  = Table.ExpandTableColumn(CT1, "Name", Table.ColumnNames(CT1{0}[Name]) )

where T1 was the previous step. The only caveat is that the first table must have all columns or they will be skiped.

Answer 1

I think there might be easier way but given your approach try to convert your list to table (column) and then expand that column:

Alternatively use Table.Combine(YourList)