Pivoting Data In Excess Of 65k Rows

I've exported some data from our finance system into Excel and now need to pivot it, problem is that there are 100k plus rows and therefore goes across more than 1 worksheet. Had a read through the forum and a number of topics about this but I'm a bit stuck. I've got as far as

1. Ungroup the 2 sheets, copied the row headers from sheet 1 to sheet 2 and then named the data in each sheet. I therefore have 2 identically arranged data ranges (albeit one is 65k rows the other 40k rows) named 'block1' and 'block2'

2. In a new workbook I've done

Data > Import External Data > New Database Query

3. I followed the wizard and slected Block1 as the table

4. In MSQuery I've edited the SQL to read

SELECT * from block1 union all select * from block2

I'm now stuck and have 2 issues

1 I'm only showing 8192 records is this a limitaion of MSQuery or has something gone wrong ?

2. I still don't know how I pivot the data