Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
saguado
Frequent Visitor

Connect and append multiple SQL tables programmatically/dinamically

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.

 

saguado_0-1654763935667.png

 

1 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:

BA_Pete_0-1654769945814.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

 

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:

BA_Pete_0-1654769945814.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.