An Excel file from SAP is added to a folder on a monthly basis.
The file contains data from a manufacturing process and lists process orders from the previous month.
Data from the file will be added to existing data from previous months in a data model in a separate Excel workbook.
The manufacturing process has four steps which are linked in the following way:
- One or more raw material batches go into making one preparation batch.
- Each preparation batch goes into making one or more filling batches which can be filled on either bottles or syringes.
- Each filling batch goes into making one or more packaging batches.
The Excel file from SAP contains hundreds of thousands of rows and lists batches from the various production steps in the same columns.
The batches are linked with various codes so it shall be possible to trace the production process by looking at the data.
The attached example file contains the following:
- A raw data table exemplifying data from SAP (yellow header row).
- A batch hierarchy table (green header rows) exemplifying rows in a Power Pivot table.
- An attributes table (blue header rows) exemplifying columns and values in a Power Pivot table.
I am trying to transform the raw data so that the manufacturing process can be followed by looking at a single row with multiple columns containing the batch hierarchy and the various alphanumeric attributes for the connected batches. I believe that Power Query, Data Modeling and Power Pivot may be relevant tools to use for this, but I have not been able to wrestle the data the way I want. Also, I would be interested in seeing all data in the green and blue tables extracted from the yellow table using formulas only, as an alternative. I am going to base several other pivot tables off of the green and blue tables.
I have been able to establish the header rows for the attributes (blue) by using a pivot table and then fetching the attributes by using and Index Match formula with multiple criteria just as an example, but I am not able to figure out how to create the batch hierarchy table (green), neither by using Power Pivot nor formulas. I am also hoping to avoid using VBA to achieve this.
I would very much appreciate if anyone are able to come up with a good solution to what I am trying to achieve. It doesn’t have to have the exact same layout that I have manually assembled, cause I will rearrange that later anyway.
Thank you in advance!