Power Query APPEND mystery

[adambc]

I have a series of Power Queries/VBA that work as follows ...

Query1 extracts all the data from a weekly source file (ThisWorkbook.Connections("Query - Query1").Refresh)
I then use VBA to perform various "transformations" including setting ColumnB.Value for every record to MMM or NNN
Query2 extracts data from the Query1 transformed table if ColumnB.Value = MMM (ThisWorkbook.Connections("Query - Query2").Refresh)
Query3 extracts data from the Query1 transformed table if ColumnB.Value = NNN (ThisWorkbook.Connections("Query - Query3").Refresh)
(basically splitting the Query1 transformed table into two separate tables with every record with ColumnB.Value = MMM in TableX and every record with ColumnB.Value = NNN in TableY)
I then apply some more VBA to delete records from TableY if TableY/ColumnA.Value also exists in TableX/ColumnA

So far so good ...

Query4 is supposed to append TableX and TableY INCLUDING the final step above (ThisWorkbook.Connections("Query - Query4").Refresh) - but it ignores the deletions!

I've found a workround by also applying the deletion to the appended table, but is there another way eg can I embed the deletion VBA in Query3 - the code I'm using is ...

Dim DelTableY As Range
Set DelTableY = Sheets("TableX").Range("A2:A999")

With Sheets("TableY")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
    For n = LastRow To 2 Step -1
        If Application.CountIf(DelTableX, .Cells(n, "A").Value) > 0 Then
        .Cells(n, "A").EntireRow.Delete
        End If
    Next n
End With
Application.ScreenUpdating = True

Any/all help appreciated ...

Thanks ...

 

[adambc]

That's the point. You then append the result of that to the previous query, thereby eliminating duplicates.

Anyway, glad you found a solution!

@RoryA

Ah, OK, two step process - got it, thanks