join two table

[Anonymous]

i have problem when join two table in edit query.

the first table called master and contain the information about the product and the second table called daily production product. the relation betwen two table is product name.

actually when i create chart for the production its give me a right chart reading but when i make join the production chart become higher. i used left & inner but it same issue.

[edhans · Accepted solution]

@Anonymous - your Master table is called a DIM or Dimension table, and the production is your FACT table, the facts of your operation.

You should load both tables into Power BI as they are. The MASTER table needs to have one unique field so you can create a valid 1 to many relationship with your production data. If you don't have one, create a compound key - say concatenating an item number and division number for example, in both tables. Just something that creates a unique key in your DIM table.

This is the start of what is called Star Schema and  it is how Power BI works best. 
Microsoft Guidance on Importance of Star Schema

Any additional questions and you can post back, or search for articles on Power BI Data models, or check out this material.

[Anonymous]

Dear,

in both table have key column called product name. the porpuse for marege table to make easy analysis, im sure the data is correct becuase when i create production chart it give me the right reading but after the join/ marege two table increase the reading for example production rate for Jan-2020  before marge is 42,000 and the reading  after the join become 50,000.

i though it dublicate the rows. actually i have huge data.

[edhans]

That is why in this case @Anonymous you don't merge. If table 1 has this:

Product ID	Product Name
A	Apple
B	Banana

and table 2 has this:

Product ID	Sales
A	10
A	20
B	10

If you merge you get this:

Product ID	Product Name	Sales
A	Apple	10
A	Apple	20
B	Banana	10

That isn't wrong, but it does duplicate records in your table 1 data.

This is why you use table 1 as your DIM table and table 2 as your fact table through a relationship in DAX, not Power Query. You are making a flat table, and Power BI is not designed to work with flat tables. It is designed to work with a Star Schema, as I linked to above.

[lbendlin]

You don't really want to join the tables in Power Query. Let the data model do the work for you - join them in Power BI.