Ideas on bringing two companies together in one Power BI report

[edhans]

I am looking for ideas on how to bring in two companies to a single Power BI report. These are sister companies, in separate databases. The technology is simple. I am asking for concept ideas, or links to best practices.

Issue:

• Both databases have a Customer DIMENSION table.
  • Some may be the same customer, but have a different customer number.
  • Some may be the same customer, and have the same customer number.
  • Some may have the same customer number, but be a different customer.
  • Obviously cannot just append the two customer tables together.
• Both databases have an Item DIMENSION table
  • 100% of the time, the item numbers that are the same are the same item
  • 100% of the time though, the cost is different due to different location.
  • Cannot append and remove dupes as I'll lose one of the company's cost info.

One idea I had was to change the Item Numbers and Customer Numbers of the 2nd company by adding a suffix or prefix letter. That guarantees no duplicates. But I am loath to change source data like that.

I cannot create a second field in either table. A second join works in Power Query, but will not work in DAX.

Just wondering what experiences others have had with this so I can avoid pitfalls down the road.

[v-yulgu-msft]

Hi @edhans,

You loaded two separate datasets for two companies into desktop, right? To combine them, you can add a custom prefix column for each table, then, append them together via the "Append queries" button in Query Editor or using UNION function in DAX.

Regards,

Yuliana Gu