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 ...

 

[RoryA]

It sounds like you should simply filter out records from Query3 that appear in Query2 (e.g. using a merge query and filtering out anything that isn't null in the merged column). Then no VBA required.

 

[adambc]

It sounds like you should simply filter out records from Query3 that appear in Query2 (e.g. using a merge query and filtering out anything that isn't null in the merged column). Then no VBA required.

@RoryA

I can see what you mean, just need to figure out how?!

Any further clues?

Thanks …

 

[RoryA]

It depends. Are column A values unique in either table?

 

[adambc]

It depends. Are column A values unique in either table?

@RoryA

Within each table the values in column A are unique - but they may appear in both tables and that’s what my VBA is doing ie if a column A value in TableY also exists in TableX delete the record in TableY - the column A is a person ID and they can only appear once in the appended table (Query4)

 

[RoryA]

If they are unique in both tables then you can do a merge query between your two queries on column A, (with whichever query you want to remove the duplicates from as the main query) using a left anti-join so that you only have rows that are unique to the first table/query. The step should look something like:

=Table.NestedJoin(#"Changed Type", {"ColumnA"}, TableX, {"ColumnA"}, "TableX", JoinKind.LeftAnti)

in your formula bar. You can then simply remove the column it added.

 

[adambc]

If they are unique in both tables then you can do a merge query between your two queries on column A, (with whichever query you want to remove the duplicates from as the main query) using a left anti-join so that you only have rows that are unique to the first table/query. The step should look something like:

=Table.NestedJoin(#"Changed Type", {"ColumnA"}, TableX, {"ColumnA"}, "TableX", JoinKind.LeftAnti)

in your formula bar. You can then simply remove the column it added.

@RoryA

Hhhmmm, not sure I’ve been clear enough?

Eg …

TableX/ColumnA.Value =1, 2, 3, 4, 5

TableY/ColumnA.Value = 5, 6, 7, 8, 9

So I’ve used my VBA code to delete the ColumnA.Value = 5 record from TableY

I then want Query4 to return TableXY/ColumnA.Value = 1, 2, 3, 4, 5 (from TableX), 6, 7, 8, 9 (from TableY)

But Query4 ignores the previous deletions from TableY, so I’ve applied similar VBA code to TableXY which does the trick, but if I can do it in Query4 even better - I’ll try it in the morning, but reading it up Left Anti Join won’t do what I need?

Thanks for your help, always appreciated .,,

 

[RoryA]

Why will it not do what you want?

 

[adambc]

Why will it not do what you want?

@RoryA

Because the merged table contains ONLY the records from the left table that don't exist in the right table ie the existing records in the right table aren't retained ...

Using my example above ... TableMerge/ColumnA.Value = 6, 7, 8, 9 (from TableY) but NOT 1, 2, 3, 4, 5 (from TableX)

But I've found an alternative approach, which I've just tested and works a treat ...

In Query4, provided all records with ColumnB.Value = MMM come first (which happens automatically when I Append TableY to TableX), then add a Remove Duplicates step to the end of Query4, Power Query will keep the first instance of any duplicates (ColumnB.Value = MMM) and delete all other instances (ColumnB.Value = NNN)

Thanks for your help - has highlighted I need to spend some more time with Power Query!

 

[RoryA]

the merged table contains ONLY the records from the left table that don't exist in the right table ie the existing records in the right table aren't retained

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

Anyway, glad you found a solution!