I need to use power query to bring the initial format to this format that i have mentioned later, i've tried Merging, Transposing, Promoting Headers and Unpivoting Columns but somehow i'm messing up somehwere.
Also need to remove the Random digit values in C column from row 4 to 6.
Here's the source excel
This below here is the final output required.
Also need to remove the Random digit values in C column from row 4 to 6.
Here's the source excel
Book4 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | OPM B. No | 236370 | 236371 | 236372 | ||||
2 | Date of booking | 23-10-2021 00:00 | 24-10-2021 00:00 | 25-10-2021 00:00 | ||||
3 | Status | Un Posted | Posted | Posted | ||||
4 | Ingredients | R001 | 0 | 1 | 2 | 3 | ||
5 | Products | W002 | 0 | 1 | 2 | 3 | ||
6 | Byproducts | B003 | 26910 | 1 | 2 | 3 | ||
Sheet1 |
This below here is the final output required.
Book4 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | B No. | Item Type | Item No | Date of Booking | Qty | ||
2 | 236370 | Ingredients | R001 | 23-Oct-21 | 1 | ||
3 | 236370 | By-Product | B003 | 23-Oct-21 | 1 | ||
4 | 236370 | Product | W002 | 23-Oct-21 | 1 | ||
5 | 236371 | Ingredients | R001 | 24-Oct-21 | 2 | ||
6 | 236371 | By-Product | B003 | 24-Oct-21 | 2 | ||
7 | 236371 | Product | W002 | 24-Oct-21 | 2 | ||
8 | 236372 | Ingredients | R001 | 25-Oct-21 | 3 | ||
9 | 236372 | By-Product | B003 | 25-Oct-21 | 3 | ||
10 | 236372 | Product | W002 | 25-Oct-21 | 3 | ||
Sheet2 |