Appending worksheets into one worksheet

I'm using Excel 2016 Okay, There is an option check box if you want to load multiple items so I selected 3 items (worksheets) but instead of selecting Load, I selected Transform. Now I see the 3 queries in the left hand side of the screen.

Now on menu bar, there is an Append Queries under Merged Queries and I selected that and I selected Append Queries As New option. Since there are 3 tables, I selected that option. I added the second and third tables to the first one because that was what Append means and hit OK.

I want to clean up the data because with the append queries, It added the headers of the 2nd and third tables as well.

How would I get rid of these rows for the clean up? There are many rows of this occurrence and they are irrelevant to the data. I only want to keep 1 header for the worksheet.

I don't want to  choose the option to remove rows that are duplicates because there may be row or item  with same 

model,description,cost and price.

The only ones I want to remove are rows with  the duplicate headers of Model,Description,Cost and Price

Go to the header row in Power Query, and filter out the other headers.  I am pretty sure that will work.  Also, if the format is the same and there will be more like these (subsequent months) you can keep all the files in a folder and load from the folder.  As you add files to the folder it will bring them into the append.

Wow! I like that folder thingy automation!

Subodh just to go to your approach, and since I'm new with power query, what is the difference between creating a connection and loading a worksheet? Isn't it once you load a worksheet a connection is automatically created? What do you mean by creating a connection onl? Then after loading all the queries or worksheets and they are showing in the right section of the screen, where is the Append query appears in the menu bar? Thanks!

I'm using Excel 2016 and that is maybe the steps differ but I saw there is a query button available but it is outside the Data/Power query menu. Also when you right click one of the queries, there is an option to append.

I chose a Text filter in column Model to 'does not equal ='Model' so I will only get the relevant data . It did removed it but if I scroll down farther, I still see 'Model' in that column. It did not clear all the 'Model' text value in that column,

Is choosing the Close and Load To option will allow to save a connection?

Also  why should I choose to create a connection instead of loading it to new worksheet by Close and Load option?

Okay, it looks like the purpose is avoiding a reload to Excel once the connection was made. I followed your method last night and I think I'm having an idea of it. 

Tom, I checked the first video link and I’m looking forward to implement that way but It is important for me to understand the parts and pieces of it . Eventually I will be doing merging queries as well and I’ve not been there yet. But because, one can arrive at same results but different steps is what only I’m eagerly interested. Thanks for another link which would surely be of great help!

I understand your situation perfectly..

This is out of my original question but since I experimented using the merged queries and selecting left anti option, 3 rows showed up that is in the first table not found in 2nd table. what is the way to delete those rows from the first table? can it be done after the merged and output in a new sheet

Thanks, zachvaldez, it was a great discussion you brought about here...