Combining several similar databases

[Anonymous]

Hi guys, 

I have a bunch of databases (100+) that all have the same schema and are living on several different SQL servers (10+). 

I have a master table on a separate database that lists the names of all my databases and on which server they live.

Now what I'd like to achieve is to combine the data from one of the tables that exists in all these databases with PowerQuery.

One option is to manually create a power query for each of them, but it would be very hard to maintain.

So I'd like to have a way to dynamically query each database.

I haven't found how to do that but I feel like it's feasible.

In a nutshell, what I'd like to do is:

- Get list of Servers and Databases from master

- For Each Database in list:

   - Get data from Table Bob

   - Append to QueryBob

Is there a way to do this with PowerQuery?

[MattAllington · Accepted solution]

Yes it can be done. In short,

1. you connect and load one of the tables
2. convert that query into a function
3. get a list of all tables into a query
4. pass the list to the function for conversion

read my article here. This is an Excel example, but the principle is the same for your use case. 

https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

[Anonymous]

Thanks Matt, that works!

[Anonymous]

And...

PowerBI service doesn't allow to refresh when functions are used in PQ...

Please vote for this!!

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9312540-make-functions-refreshabl...