Creating entries in Table A from Table B

[CBartkowiak]

Dear experts,

I´m a newby on Power BI /Power Query and I hope, you can help me to solve my problem. Thx in advance.

I have two tables (Table A and Table B) and I´d like to use B to create entries in A. At the end I´d like to have one row for each category for each customer. If the row got created, the the value should be "0". I hope, I was able to explain my issue. Below the tables as example

Table 1
customer	category	value
a	aa	12
a	dd	5
b	ee	21
c	bb	29
c	ff	21

Tabel 2

category

aa

bb

cc

Final
customer	category	value
a	aa	12
a	bb	5
a	cc	0
b	cc	21
b	aa	0
b	bb	0
c	bb	29
c	cc	21
c	aa	0

Thank you

[AMeyersen · Accepted solution]

I'd do it in several steps:

1. create a subquery containing all distinct customer values
2. do a cross join with the category table (get each combination of customer/category exactly once)
   https://learn.microsoft.com/en-us/power-query/cross-join
3. do a full outer join on your example "Table1" with the result query of step 2. Use columns Customer and Category as join criteria
4. add custom columns which contain the correct results from each part of the join
5. delete unnecessary indermediate columns

[CBartkowiak · Accepted solution]

HI,

thx for your help and sorry for my late reply. It worked for me.

[ryan_mayu]

why in the final table b, bb is 21 and c, cc is 21? could you pls explain how to get the output?

[CBartkowiak]

Hi,

I´m sorry for this confusion. This is just an issue on copy & paste on creating the test table

[ryan_mayu]

Does @AMeyersen 's solution work for you? if not ,pls correct the expected output.

[CBartkowiak]

HI,

thx for asking. I didn´t find the time yet to test it, but I´ll leave a comment, when tested.

[AMeyersen · Accepted solution]

I'd do it in several steps:

1. create a subquery containing all distinct customer values
2. do a cross join with the category table (get each combination of customer/category exactly once)
   https://learn.microsoft.com/en-us/power-query/cross-join
3. do a full outer join on your example "Table1" with the result query of step 2. Use columns Customer and Category as join criteria
4. add custom columns which contain the correct results from each part of the join
5. delete unnecessary indermediate columns