Forum Discussion
Join a calculated table with an 'original' table
Hi all,
I am trying to combine a calculated table with an 'original' table.
Table A holds transactions per row. Each transaction is to be allocated to multiple customers the breakdown of share per transaction per client is available in table B. Table B is the calculated table.
As output I need everyt transaction to duplicate based on the participating clients. I have visualised this below.
The reason this needs to be a new table is because after merging, additional calculations / columns will be added.
Thank you in advance,
Bruce
Table A
| CLIENT GROUP | AMOUNT |
| US001 | 10 |
| NL001 | 10 |
| NL001 | 20 |
Table B
| CLIENT GROUP | CLIENT | PARTICIPATION |
| US001 | TRUMP | 100% |
| NL001 | RUTTE | 50% |
| NL001 | WILDERS | 40% |
| NL001 | TRUMP | 10% |
New table
| CLIENT GROUP | AMOUNT | CLIENT | PARTICIPATION |
| US001 | 10 | TRUMP | 100% |
| NL001 | 10 | RUTTE | 50% |
| NL001 | 10 | WILDERS | 40% |
| NL001 | 10 | TRUMP | 10% |
| NL001 | 20 | RUTTE | 50% |
| NL001 | 20 | WILDERS | 40% |
| NL001 | 20 | TRUMP | 10% |
Hi Anonymous ,
Because Table B is a calculated table, and it won't be displayed in Query Editor, so you might not use the Merge feature to combine the two tables into one.
As a workaround, you can create relationship between the two tables on field [CLIENT GROUP], then put all the desired fields into the same table visual.
Or you may include the Table A in the formula which created the calculated Table B.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-xicaiCommunity Support
Hi Anonymous ,
Because Table B is a calculated table, and it won't be displayed in Query Editor, so you might not use the Merge feature to combine the two tables into one.
As a workaround, you can create relationship between the two tables on field [CLIENT GROUP], then put all the desired fields into the same table visual.
Or you may include the Table A in the formula which created the calculated Table B.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- mwegener
Most Valuable Professional
Hi Anonymous,
please use Power Query for these kinds of transformations.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
