Power Query - Transforming & organizing VERY messy data. Need help/guidance.

Good day,

I've been tasked at the brewery that I work for to compile all of our data so that we can begin to compare and contrast each batch of beer we make in a more productive and clean fashion. I consider myself pretty handy with excel, but I am just starting to learn power query & Power Bi and am still a novice at it. A lot of the tutorials that I've been watching usually have pretty clean data that is somewhat easy to manipulate. Their data is usually already in tables, or at least categorized properly with very little complications to overcome. I have a decent grasp of the basics, but am finding myself struggling where I should even start with my task and how to slowly chip away at it.

The attached workbook contains an example of what our files typically look like. I've filled them in with temporary data. There are also a few sheets I've hidden as they are usually helper sheets that contain some of the selections for the drop down menus on the sheet. The primary sheets I am trying to compile are the four different batch sheets. They all contain the same fields. My objective is to make the layout and transform the data into a similar fashion as the attached photo. There are a lot of merged cells in the actual document and that makes for a very messy layout in the initial Power Query page. It is a little daunting on where to begin

One way I have tried tackling this is by creating multiple queries for the different sections on the sheet and then appending them together, but doing some research said that is not good practice and can lead to problems in the future.

If someone could help guide me towards a good tutorial or how I should go about transforming the data that would be lovely!


Cheers