Joining 2 tables (Budget & Invoice) with different level of info in each

[Alex_192]

Hello everyone,

I am quite new on PowerBi but despite some hours of youtube tutoriel and reading on the net, I can't manage to join my two tables in Power BI..

I would really appreciate if someone could help me 🙂

Let me explain:

I have a 1st table (invoice database) with invoice where I have 1 line = 1 invoice with:

- salesman name (A, B, C, D, E, G) - here the salesman "G" is not in the budget table

- product name (Pdt1, Pdt2, Pdt3, Pdt4, Pdt 6) - here the Pdt 6 is not in the budget table

- sales (in value)

- quarter

My second table with the budget where I have 1 line = 1 quarterly budget per product per salesman with:

- salesman (A, B, C, F) - here the "F" salesman is not in my invoice database

- product name (Pdt1, Pdt2, Pdt3, Pdt4, Pdt 5) - here the Pdt 5 is not in the invoice database

- quarter

- Budget (in value)

How do I join those 2 tables to get:

per salesman (A, B, C, D, E, F and G) and per product name (Pdt1, Pdt 2, Pdt 3, Pdt 4, Pdt5 and Pdt 6) the sales realized and budget per quarter ?

(I can't manage to have the full list of salesman + the full list of product name with the correct allocation of both sales & budget)

Thank you very much for you help!

Alex

[v-qiuyu-msft]

Hi @Alex_192,

I use some sample data and transform data use Unpivot columns and Merge queries as New in Query editor, please download attached .pbix file to see if it meet your requirement.

Best Regards,
Qiuyun Yu

[Greg_Deckler]

Perhaps create a combination key calculated column like:

Key = CONCATENATE([salesman name],[product name])

Create one of these in both tables and then relate the tables together.

You could also take a look at doing a Merge query to truly merge the tables together.