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

Can you upload the mock up as well, please?

Done. I've labeled it, "Ideal Query Layout". Ultimately, my goal is to get all the fields and data onto one row per Batch of beer. Each batch of beer's data is comprised on one excel sheet in a workbook.

Thank you for taking your time to look at it.

It is late evening here. If nobody else looks overnight, I'll take a look tomorrow morning.

This is going to be above my pay grade, however there are other PQ experts who will see this and respond, I am sure. Good luck!

Originally Posted by AliGW

This is going to be above my pay grade, however there are other PQ experts who will see this and respond, I am sure. Good luck!

Appreciate you taking the time to take a look at it. You maybe able to help with how I previously tried to go about solving my problem.

One way I went about doing it was creating a series of different queries for the different sections of the sheet. So a section for the information in the header, one for the mash in data, one for the lautering data, so on and so fourth. Once they were all divided, I appended them all into another query. One they were all appended, I was able to fill up the data and delete the remaining rows to get everything in one line. However, this seems suboptimal. If I continued down that way as a solution Do you think that is a viable way solve my issue or will that lead to complications down the road?

VBA extract

Sub transform2Query()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Current As Worksheet
Dim ar()
Dim i As Long, j As Long, m As Long, n As Long

'cAddr = Array("F2", "B2", "Tab", "I2", "L2", "A5", "B5", "C5", "D5", "A6", "B6", "C6", "D6", "A7", "B7", "C7", "D7", "D16", "D17", "D18", "D19", "I4", "J5", "J7", "J8")

Application.ScreenUpdating = False

Dim strFilename As String: strFilename = "Ideal Queary Layout.xlsx"
Set wb1 = ThisWorkbook
wb1.Activate
Set ws1 = Worksheets("Sheet1")

strFilename = "C:\Users\Owner\Desktop\BDS_Example_PowQuery.xlsx"
Set wb2 = Workbooks.Open(Filename:=strFilename)
wb2.Activate

n = 0
                                   
For Each Current In Worksheets ' Loop through all of the worksheets in the active workbook.
   ' MsgBox Current.Name
    If Left(Current.Name, 11) <> "Brew Data B" Then Exit For
    
    Current.Activate
    n = n + 1
    ReDim Preserve ar(1 To 25, 1 To n)
    
    Batch_no = Right([B2], 1)
    ar(1, n) = [F2]  ' Date
    ar(2, n) = [B2]  ' Beer Brand
    ar(3, n) = Batch_no ' Batch Number
    ar(4, n) = [I2]  ' Fermenter
    ar(5, n) = [L2]  ' Brewers
    
    m = 6
    For i = 5 To 7  ' Company / Malt / Location / Amount
        If Cells(i, 1) <> "" Then
            For j = 1 To 4
                ar(m, n) = Cells(i, j)
                m = m + 1
            Next j
        End If
    Next i
    For i = 1 To 4 ' Minerals
        ar(m, n) = Cells(i + 15, 4)
        m = m + 1
    Next i

   ar(22, n) = [I4]  ' Start Mash
   ar(23, n) = [I5]  ' End Mash
   ar(24, n) = [I7]  ' Strike Temp - Steeping
   ar(25, n) = [I8]  ' Strike Temp - Mash
Next Current

With wb1
    ws1.Range("A15").Resize(UBound(ar, 2), UBound(ar, 1)) = Application.Transpose(ar)
End With

Application.ScreenUpdating = True

wb1.Activate

End Sub

I am a novice re PQ but I doubt if this can be done with PQ which works from structured tables.

You will need a naming convention for the tabs: the above assumes "Brew Data Bn". I use the number in B2 text to determine batch number.