Connect and append multiple SQL tables programmatically/dinamically

[saguado]

Hi,

I have an SQL database where every monthly data is inserted into a table, so all tables in database have the same schema. And I would like to know if its possible to create M query to append all of them dinamically (like CSV folder). Below screenshot shows tables names. I'am able to capture table names with simple SQL SELECT from INFORMATION_SCHEMA.COLUMNS, this is giving me a table with all the table names, but don't know how to iterate, connect and append all of them. Thanks in advance for any help or support.

S.

[BA_Pete]

Hi @saguado ,

I would very strongly suggest that you speak with your DBA and get them to store all of this information in a single table on your DB. It's pretty bad form to have tables containing similar data to be segmented like this on a DB, especially by date parameters.

I think trying to manage this situation via Power Query would be a coding and performance nightmare.

Pete

[saguado]

Hi Pete,

Thanks for yur prompt answer and I fully agree with your comments, but, the issue is that this database is created by a commercial software, and we cannot change this, so I need to find a way to merge them dinamically.

S.

[BA_Pete · Accepted solution]

Ok, fair enough, but there must still be someone managing the DB that can create a Stored Procedure or similar to combine all these tables.

Anyway, that aside, to do this in PQ you do the following:

1) Connect to any one of the tables from PQ to generate your Source and Navigation steps.

2) Once imported, delete your Navigation step from the Applied Steps list. This should leave you with a table list, something like this:

3) Filter this list as you would any other table to end up with a list of all the tables you want to append. For your scenario, you would probably apply a filter to the [Name] column something like this:

Text.StartsWith([Name], "HE_")

Leaving the criteria generic like this ensures that the table selection is dynamic as new tables are added.

4) Create a custom step like this:

Table.Combine(previousStepName[Data])

This should result in a combined table, including all tables that featured in your filtered list.

Pete