Using Power BI for multiple similar databases

[Anonymous]

Hi everybody,

I'm trying to find the best way to create a consolidated dashboard for my holding company. 

Some of my companies are using the same software but each one has a separate database. 

I would like to implement a dashboard with all my clients, suppliers, articles, invoices and so on.

I can extract automatically CSV files for each database. Each CSV has the same name for all the companies (ex: Clients.CSV). 

How can I do into Power BI to consolidate all the similar files together while not loosing the fact that it is from company A or B, for example ? 

How can I handle the IDs which are going to be similar in all the databases but would absolutely not represent the same thing ? 

Is someone able to help me ? or to send me some documentation to learn about it?

Have a nice day.

[amitchandak · Accepted solution]

@Anonymous , My advice would be to consolidate these into a database with company id/tenant id in each table and have new keys to join with other tables. so that you can have one key join in power bi

Else , say you have Table A for 2 companies.

Bring both on them to power BI, Add company id column if not available at the source. Create concatenated key 

key = [Company ID] & " - " [Table ID]

or

key =  [Company ID] *1000000000 +  [Table ID]  // multiple to make sure table can not overlap with company if

Merge two table A in power query. Do the same for other tables

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

Thanks Amitchandak. Going to test it.