Bar and line chart - relationship between tables

[TiagoLopes]

Dear all,

What I want to do:

I have several tables with Data. I want to have a graph to show (bar=value) from table 1 and (line=value) from table 5 s have a 'Date' field and 'Value' field.

I want to have a line and column chart where I can see the relation of the 2 'Values' for the same date.

One of the problems is that this 2 tables don't have a direct relationship between them.

If I do the 2 graphs separatelly I will have:

It's basically ok, I can't just combine them.

Both Tables have this format:

In Power Bi as first step I am doing Query so I can display data Month by Month. 

Problem is that I will not have Unique values on column A anymore, so I will not be able to create a Many to One relationship anymore!

Looking at my current relationship I have the following:

Table 2= main table - I have no 'Date' field on that table. In table 2 I have the relation between 'A PN' and 'B PN'.I have many 'A/B PN' in table 2.

Table 1 = I have Unique 'A PN' and the sales month by month. Problem is that it will not be unique anymore after Query. So I cannot do Many to One relationship between 2-1.

Table 3 = I have unique 'B PN' and the relation with 'Tool'. Tool is not unique. I have different 'B PN' with same tool

Table 4 = table just to show summary relation between 'B PN' and 'Tool' (this is already in table 3). My current one to one relationship is 'B PN' to 'B PN'

Table 5 = Finally I have the same 'Date' as in Table 1. Each tool as a different value for each month (previous table), but again after Query I cannot do Many to one relationship between table 4 and 5.

How can I manage this relationships or what should I do to get the graphic I want ?

Hope it is clear, if not please let me know.

Thank you for support.

Tiago

[v-chuncz-msft · Accepted solution]

@TiagoLopes 

You may use Append Queries (Table.Combine) in Query Editor or UNION Function (DAX) to create a new table.

[TiagoLopes]

Hello,

Someone can support?

Thank you