Power Query APPEND mystery

A

adambc

Active Member
Joined
Jan 13, 2020
Messages
384
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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 ...

VBA Code: 
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 ...
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
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.
 
Upvote 0
RoryA said:
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.
Click to expand...
@RoryA

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

Any further clues?

Thanks …
 
Upvote 0
It depends. ;) Are column A values unique in either table?
 
Upvote 0
RoryA said:
It depends. ;) Are column A values unique in either table?
Click to expand...
@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)
 
Upvote 0
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:

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

in your formula bar. You can then simply remove the column it added.
 
Last edited:
Upvote 0
RoryA said:
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:

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

in your formula bar. You can then simply remove the column it added.
Click to expand...
@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 .,,
 
Upvote 0
Why will it not do what you want?
 
Upvote 0
RoryA said:
Why will it not do what you want?
Click to expand...
@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!
 
Upvote 0
adambc said:
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
Click to expand...
That's the point. You then append the result of that to the previous query, thereby eliminating duplicates. :)

Anyway, glad you found a solution!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Matrix match across identical tables in Power Query
Replies
3
Views
517
smozgur
smozgur
D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
L
  • Question
Excel Power Query Refresh Does Not Update
Replies
2
Views
678
legalhustler
L
J
  • Question
Power Query: The same table getting appended with every refresh causing duplication
Replies
4
Views
597
smozgur
smozgur
T
  • Question
Aggregation tables do not include real-time data
Replies
0
Views
165
TamirBasin
T

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top