Dynamic or Iterated SQL in data source?

[Doug68]

This report is based on a commercial software app, so the structure of the data is what it is, so there's no point in complaining about it.  The data in this case is in a Oracle DB I'm accessing via ODBC.

There's 2 parts to this...

Part 1.  I have a data source based on a query that gives me a series of users (schemas).  The list is dynamic i.e. users can be created and dropped on a day by day basis.

Part 2. Each user has tables with the same names, so if it was not dynamic I'd create a simple union query and the job would be done, however its not.  So I need to iterate through the results from Part 1 to dynamically create a unioned dataset of the different users (with a column in there that has the username).

I've tried to look at the List function and failed, Parameters as such don't look like the way to go, so I'm bit stuck on how to tackle the issue.  Any advice on how to go about this would be appreciated.

[ImkeF · Accepted solution]

Hi @Doug68 ,
If you connect to a database, Power Query will usually create some steps automatically for you. One of theses steps will usually contain a list of all tables of your database. Delete all steps that come after that.
Then, convert your list from Part 1 to a table and merge it to Part 2 in the mode of an inner join. That will act as the filter you need and return only the user tables from the list in Part1. Expanding the content will give you that unionated result.