Get and Transform aka Power Query Assistance (1 Viewer)

[JMongi]

Time to crash the Excel party!

So, I have a fairly simple need from an Excel spreadsheet. I need to use Power Query or whatever its called now to merge a bunch of worksheets into a master list. I've tried to follow two different "step-by-step" instructions and they both failed almost immediately!

As far as I understand it, I need to specify all of the separate sheets and exclude two sheets, the one that the query will paste to and another sheet that will use the updated list.

Final Spreadsheet structure should be:
Sht A - Working List that pulls links from Sht B
Sht B - Destination of Query, Will be deleted and regenerated as C through Z change.
Stt C thru Z - Source of data. All formatted the same with the same columns and headers.

Query: Merge C though Z and put in B

I'm still trying to find a decent guide but thought I'd try out the Excel section here! Thanks!

 

[Minty]

Power Query likes data in named ranges or tables. If all your data in your worksheets is in tables, PQ can simply gather the data you need and transform it.

You can automate it, (from Access) if you need to, but it's a bit of a learning curve.

 

[JMongi]

I don't need to touch Access for this particular issue. And I know that PQ can do what I need it to. I just need to actually DO it and the documentation I have found so far has been lacking.

This first tutorial I couldn't even connect to the source data.
When I connected to it on my own, I can't find where to control the specific sheets using a <> to exclude the two sheets mentioned as the number of all other acceptable sheets may/will fluctuate.

 

[JMongi]

Power Query likes data in named ranges or tables

Is this the issue? So a table is different than a specific worksheet. I see. That's a lot more work (there are 57 sheets plus the two extra I need)
I generated these subsheets from an old master list using VBA. The subsheets now need to be recombined into a new master list. I thought I'd find VBA code as well, but all I keep finding are references to PQ.

 

[Minty]

Tables are easy to create just click anywhere in the data and click insert in the toolbar and select table. Job done.
I have the command in a shortcut menu as I use them all the time.

 

[isladogs]

I recommend you purchase this book
M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query: Amazon.co.uk: Puls, Ken: 9781615470341: Books
It is an excellent guide to PowerQuery which is indeed now called Get and Transform

 

[JMongi]

Thanks Colin! I'll have to check that out.

In the meantime, I think PQ is overkill. I used a VBA script to create the subsheets and I used one to merge them together.

Parse Code:

Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer

    'This macro splits data into multiple worksheets based on the variables on a column found in Excel.
    'An InputBox asks you which columns you'd like to filter by, and it just creates these worksheets.

    Application.ScreenUpdating = False
    vcol = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Default:="3", Type:=1)
    Set ws = ActiveSheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
        On Error Resume Next
        If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
            ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
        End If
    Next

    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear

    For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
        End If
        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
        'Sheets(myarr(i) & "").Columns.AutoFit
    Next

    ws.AutoFilterMode = False
    ws.Activate
    Application.ScreenUpdating = True
End Sub

Merge Code:

Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range
Dim wb As Workbook
Dim mtr As Worksheet
Dim ws As Worksheet

Worksheets("Consolidated").Cells.Clear  'Clear Target Sheet

Set mtr = Worksheets("Consolidated")    'Set Master sheet for consolidation

Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)

'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column

Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
     'except the master sheet from looping
     If ws.Name <> "Master" Then
        ws.Activate
        lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
        lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
        'get data from each worksheet and copy it into Master sheet
        Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy
        mtr.Range ("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
           End If
Next ws

Worksheets("Consolidated").Activate

End Sub

 

[JMongi]

The only issue with the merge code is that

If ws.Name <> "Master" Then

doesn't seem to be excluding the "Master" worksheet. I still get extra entries. Once I sort that out, I'll be golden.

 

[JMongi]

Well, I ended up nesting them and it worked.

Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range

Worksheets("Master").Activate
Cells.Clear

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")

Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)

'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column

Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
     'except the master sheet from looping
     If ws.Name <> "Master" Then
        If ws.Name <> "Consolidated" Then
            ws.Activate
            lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
            lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
            'get data from each worksheet and copy it into Master sheet
            Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
            mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
        End If
    End If
Next ws

Worksheets("Master").Activate

End Sub

 

[isladogs]

Glad you have a solution

 

[Isaac]

How do you like power query / get and transform?

 

[JMongi]

@Isaac - Looks useful. Also, extremely unintuitive to use.

I usually don't have much difficulty learning new computer programs. A little experience, pattern recognition and patience has helped me learn many computer programs and find many functions I didn't know existed but I was able to look where it made sense for them to be based on other programs I've used.

Trying to navigate the UI and figure out what it was trying to DO and how it was trying to PRESENT it to me was hard. It may not have helped that the first two online articles I found were a hot mess when it came to how to use it. So, maybe that is affecting my opinion. I eventually started to wrap my head around what it was trying to do but I admit it was bit more frustrating than expected.

After I discovered the additional hoops I needed to jump through just as a baseline, I realized that PQ was overkill for what I was trying to do. So, I turned back to a VBA based solution and voila!

 

[JMongi]

Ok, I'm already disappointed in the performance of this code I borrowed from the internet. So, I'm trying to fix it/streamline it. I'm going to circle back to the merge code at a later date. For now, I need to get my column copying working.

I'm trying to copy source columns B&D to destination columns A&B and source columns C&D to destination columns D&E.

Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers, consol1, consol2 As Range

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")
Set con = Worksheets("Consolidated")
Set wb = ThisWorkbook

'Merge code that works for now
'Leaving out

'copy the data from the master sheet to the consolidate sheet
mtr.Activate
Set consol1 = mtr.Range("B, D")
Set consol2 = mtr.Range("C, D")

con.Activate
Cells.Clear
consol1.Copy con.Range("A1")
consol2.Copy con.Range("D1")
consol.Rows(1).Delete

'sort the data
con.Sort.SortFields.Clear
Range("A1:B2001").Sort Key1:=Range("A1"), Header:=xlNo
Range("D1:E2001").Sort Key1:=Range("D1"), Header:=xlNo

End Sub

Currently I get a type mismatch error on setting consol1 and consol2.

 

[Minty]

Dim headers, consol1, consol2 As Range

Means headers, consol1 ae declared a Variants, not ranges.
You have to declare individually in VBA I'm afraid

Dim headers as Range, consol1 As Range, consol2 As Range

 

[JMongi]

This is VB in Excel which COULD be a slightly different animal.
Also, I just copied the structure of the first line (which is not mine). LOL!

 

[JMongi]

Well, now a type 1004 error. Ugh. I just need to get this working quickly. Now I have a meeting to run to. *sigh*

 

[JMongi]

Ok, I got it working. Elegant it ain't. But it works. If some of you code masters could give me any tips for the following, let me know. Right now it runs REALLY slowly. I don't think the ws.activate is needed, but maybe it is. Anyway, I don't have time to troubleshoot a change so I will get back to it. Here is the code in question in the meantime...

For Each ws In wb.Worksheets                'loop through all sheets
    If ws.Name <> "Master" Then             'except the master sheet from looping
        If ws.Name <> "Consolidated" Then   'except the consolidated sheet from looping
            ws.Activate
            lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
            lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
            'get data from each worksheet and copy it into Master sheet
            Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
            mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
        End If
    End If
Next ws

 

[Isaac]

Is this going to be deployed and used by various users on a regular basis? If so, I'll go into my soapbox on the extreme dangers (and eventual catastrophe) of unqualified things, like Range (on which worksheet?) and depending on Select and Activate in Excel vba. If it's a one time thing and it works for you, I'll skip that.

The single biggest things I've done to speed up Excel vba code is turn ScreenUpdating off and, depending on the workbook, Calculations to xlManual during runtime. But I would never run unqualified references like that - eventually someone will run it who has 3 other Excel's open (and being worked on with their keystrokes and mouse), and your dependence on Activate will blow up

I'd at the very least change it to this on principle:

For Each ws In wb.Worksheets                'loop through all sheets
    If ws.Name <> "Master" Then             'except the master sheet from looping
        If ws.Name <> "Consolidated" Then   'except the consolidated sheet from looping
            'ws.Activate
            lastRow = ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
            lastCol = ws.Cells(startRow, ws.Columns.Count).End(xlToLeft).Column
            'get data from each worksheet and copy it into Master sheet
            ws.Range(ws.Cells(startRow, startCol), ws.Cells(lastRow, lastCol)).Copy _
            mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
        End If
    End If
Next ws

 

[Isaac]

This is VB in Excel which COULD be a slightly different animal.
Also, I just copied the structure of the first line (which is not mine). LOL!

Definitely follow Minty's advice, applies to all vba. At the very least for dev purposes, you'd hate to lose intellisense and smarter Compiling logic which you'll gain from properly declaring all as Range

 

[JMongi]

Ooooh....screen updates off, that would probably significantly impact this.
Soap box away. I hate how this code is structured but I didn't have time to learn and roll my own. Once the fire dies down I'm going to go back and rewrite it properly.

As far as activate goes, is it even needed in VBA? Shouldn't all the commands work without the sheet being active? The only thing I'd have to wrap my head around is the actual cell location coding they used. Other than that....beh.