Consolidate data from multiple tables

[LeonPouwels]

I'm mapping a food production process using Power BI. I want to be able to track ingredients from start to finish, i.e.: I enter the Supplier Code of an ingredient and see which final products it ended up in.

The production line is about 10-16 steps and each step in the process is a StockRegistration table with a source and destination LotNr. Because each step can have multiple sources I'm unable to use normal Parent-Child techniques and I have resorted to chaining a simplified copies of the StockReg table via bridge tables with eachother. This results in a series of tables with all the LotNrs I need to see all the steps in the process. 

Question: How can I consolidate the LotNrs from all tables together in 1 single table?

When I use DISTINCT(UNION(TABLE1,TABLE2, etc)) it does not take into account that the tables are filtered by their predecessors and I just end up with a calculated table with all LotNrs in de StockReg table. 

Short version of the data model:

[v-eachen-msft · Accepted solution]

Hi @LeonPouwels ,

You could use Merge Queries in the video below.

[sturlaws]

@LeonPouwels,

could you create a representative sample report, like the data model of you screenshot?

Also, in order to help you, it would be helpfull to see know what you want the output to like, e.g. a description of columns, or create a mock-up in excel.

cheers,

S