Forum Discussion
Helper IIIInner join two created tables to create tree structure
I would like to build a "tree structure" for BOMs in a report.
Current method:
I import all BOMS in a table such as this:
| Article | Quantity | Component |
| A | 1 | D |
| A | 2 | B |
| A | 1 | T |
| B | 1 | X |
| B | 1 | Y |
| B | 1 | Z |
| D | 3 | G |
| D | 2 | H |
Then I create a support table to identify which Components are "assemblies" which are also broken down, in the list above that would be B and D.
Based on my support table, I create one table called Level 1 where the top level articles are shown:
| Article | Quantity | Component |
| A | 1 | D |
| A | 2 | B |
| A | 1 | T |
| B | 1 | X |
| B | 1 | Y |
| B | 1 | Z |
| D | 3 | G |
| D | 2 | H |
And a different table, Level 2 where the assemblies are shown
| Article | Quantity | Component |
| B | 1 | X |
| B | 1 | Y |
| B | 1 | Z |
| D | 3 | G |
| D | 2 | H |
Then I create a realation in between the Component in Level 1 and Article in Level 2. this is the put in a visual and when I also show items with no data, I get a tree structure.
However, I was thinking about doing this as a calculated table instead, innerjoin Level 1 and Level 2, but since I need first to do my check step, I can't take it straight from my database, thus I can't use PowerQuery for this..
What would be the best way to solve this?