Forum Discussion
Connect and append multiple SQL tables programmatically/dinamically
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
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:
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