Merge two worksheets using PowerQuery

[eduzs]

I have two workshets "ShtA", "ShtB", with columns A-H with data, I want to use PowerQuery to write in "ShtC" all "ShtB" data plus the data that is in ShtA but not in ShtB using clomun "A" to verify this.
How can I do that?
Thanks.

 

[sandy666]

using XL2BB show structure of data from both sheets

anyway you can

• use append feature
• use merge feature with key column but you don't want it
• use index column for each table as key columns and merge

but I don't know the structure of your data so that's all what I can say

 

[macfuller]

Without knowing the value in column A to validate, we can only answer in generic terms. First, it's easiest for naming if you have the data in each sheet in a table.

Load each table into Power Query using the "From Table/Range" ribbon choice in the Data menu. Let's say the queries are called tblA and tblB.

You will want to merge tblA with a left join on tblB based on the values in column A.

The new merged query (tblC) will have values where tblA and tblB matched. Delete the rows where this happens... now you will have only rows from tblA that did not match to anything in tblB.

Now append tblC query to tblB query (tblD).

Finally, load tblD to a new worksheet - make queries tblA, tblB, tblC Connection Only as there's no need to load them to the data model.

 

[eduzs]

I managed to create two tables TblA and TblB, now I need to creat a query that returns all records from TblA plus records that are in TblB but not in TblA. How can I do that with PowerQuery?

 

[eduzs]

Thanks! I managed to do it using append feature and removing duplicates.
Much easier than I thought.

 

[sandy666]

Congrats!