Forum Discussion
Data Source to be mapped into 4 tables
Source : Here
Problem : From the Source Sheet, Source (R&D) R1, I would like a workflow for it to make 4 Tables. If it's possible to create 4 tables at once would be ideal, where the source would be updated and the Tables would all be updated co-currently. Tables to be created inside Power BI Table View. Tables to be created, refer sheet Main Table, Table 1, Table 2, Table 3. I've also added mapping details into each Table sheet. The Main Table is to be extracted by SSI Item + ST Item along with SSI Type/Grade + ST Type/Grade. The Form ID and Yard Location is duplicated accordingly to the amount of Item + Type/Grade. Supplier Name is generated from Supplier Name 1 - Supplier Name 10. Below is the Expected Main Table, refer Sheet Main Table, :
To explain for each row mapping, Form ID = Form ID, Yard Location = Yard Location, Supplier Name = Supplier Name (1-10), Material = (SSI/ST) Item (1-10) [ SSI Item 1/ST Item 1], Type/Grade = (SSI/ST) Type/Grade (1-10) [ SSI Type/Grade 1/ ST Type/Grade 1], Quantity = SSI Quantity (1-10), DO No = SSI DO No (1-10), DO Date = SSI DO Date (1-10), Stock Take Location = ST Location (1-10), Stock Take = ST Quantity (1-10), MRDO No = ST MRDO No (1-10).
Balance QTY :
As this is a constantly updating type of future Dashboard, Quantity and Stock Take will always be updated where when mapped old data + new data and Balance QTY will calculate Quantity - Stock Take = Balance QTY.
Table 1 : Straightforward explanation for what is needed on the sheet itself. Material, Type/Grade and Quantity is all from SSI only.
Table 3 : Straightforward explanation for what is needed on the sheet itself. Material, Type/Grade, Stock Take Location, Stock Take, and MRDO No is all from ST only.
Table 2 : Material and Type/Grade is mapped with SSI and ST. Reason why Quantity is empty is because there was no history of Yard Location for the following Material and Type/Grade.
Please refer to the material provided at Source : Here
Would like to know if this scenario is workable and the solution.
4 Replies
This can be done but you will need to run four separate queries, one for each sheet. You can create your first query and then create duplicates and change the target sheet etc.
Is it possible for one query to generate 3 other, for example the source generated to the main table then have it duplicate to other queries with specific columns or details of a column as wanted in the source?
yes, that is possible. Just not dynamically. You need to define these queries.
