Forum Discussion
Using Power BI for multiple similar databases
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.
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
2 Replies
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