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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Combining several similar databases

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?

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Thanks Matt, that works!

Anonymous
Not applicable

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...

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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