Reply
Regular Visitor
Posts: 29
Registered: ‎09-19-2015
Accepted Solution

How to connect multiple database with same table in Power BI

Hi Everyone

 

I have a unique situation where my client has 23 databases for their companies with same table and views. they want a report in which they can filter the report by each company and view the report .

 

My delima is how can i merge multiple databases table in powerBI , if anyone ran into this issue previously, any help will be great.

 

Thanks


Accepted Solutions
Regular Visitor
Posts: 29
Registered: ‎09-19-2015

Re: How to connect multiple database with same table in Power BI

Hi Ankit, 

 

Is it possible to get an example on how to create a database connecion parameter . Thanks 

View solution in original post


All Replies
Highlighted
Super User
Posts: 1,662
Registered: ‎12-15-2015

Re: How to connect multiple database with same table in Power BI

@Rahulsingh What you want to do is in powerbi desktop create two parameters that contains server and database name for your databases (i am guessing you have sql server database). Then create all your reports from first database in powerbi desktop and save that as powerbi template file (.pbit). Next when you open that template file it will ask you for values for those parameters and you can enter in that for all other databases. By this you won't have to create seperate pbix for each database and you can use just one template file for all your databases.

 

Hope it make sense.

Established Member
Posts: 221
Registered: ‎02-18-2016

Re: How to connect multiple database with same table in Power BI

If you want one report where you can select the data you want then all the data has to be in one data model ...

 

  • You can use the query editor to create 23 separate queries (sounds hard but you just create a query for one of the views and then duplicate it and change the data source).
  • You probably want to add the same column to each query indicating which database it's from
  • You'll then append all the queries together

The nice thing is once you've built this you'll never have to build it again - this is you being nice to your future self Smiley Happy

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
Regular Visitor
Posts: 29
Registered: ‎09-19-2015

Re: How to connect multiple database with same table in Power BI

Hi Ankit, 

 

Is it possible to get an example on how to create a database connecion parameter . Thanks 

Moderator
Posts: 2,588
Registered: ‎03-06-2016

Re: How to connect multiple database with same table in Power BI

@Rahulsingh

 

You just need to create a query parameter and populate it with database name, then use this parameter in source Power Query. See screenshots below:

 

Capture22.PNG

Capture33.PNG

Capture11.PNG

Regards,

Member
Posts: 117
Registered: ‎06-01-2018

Re: How to connect multiple database with same table in Power BI

I'm in a similar situation,

what I want is to do is to read tables from the different database on Azure, and then, for example, calculate the net profit for a period from all database.

 

can u post a guide how to do that?

 

Member
Posts: 117
Registered: ‎06-01-2018

Re: How to connect multiple database with same table in Power BI

@v-sihou-msft

 

I'm in a similar situation,

what I want is to do is to read tables from the different database on Azure, and then, for example, calculate the net profit for a period from all database.

 

can u post a guide how to do that?