cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Latifa
New Member

join two table

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.

 

 

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

@Latifa - 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User III
Super User III

@Latifa - 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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
Super User III
Super User III

That is why in this case @Latifa 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
lbendlin
Super User III
Super User III

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors