Forum Discussion
Creating entries in Table A from Table B
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
I'd do it in several steps:
- create a subquery containing all distinct customer values
- 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 - do a full outer join on your example "Table1" with the result query of step 2. Use columns Customer and Category as join criteria
- add custom columns which contain the correct results from each part of the join
- delete unnecessary indermediate columns
HI,
thx for your help and sorry for my late reply. It worked for me.
6 Replies
- AMeyersen
Resolver III
I'd do it in several steps:
- create a subquery containing all distinct customer values
- 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 - do a full outer join on your example "Table1" with the result query of step 2. Use columns Customer and Category as join criteria
- add custom columns which contain the correct results from each part of the join
- delete unnecessary indermediate columns
ryan_mayuSuper User
why in the final table b, bb is 21 and c, cc is 21? could you pls explain how to get the output?
Hi,
I´m sorry for this confusion. This is just an issue on copy & paste on creating the test table
HI,
thx for your help and sorry for my late reply. It worked for me.