Combine Two Tables into One

[Mack23]

Hello,

I have two data tables in Excel that have the same exact headings from two workbooks that I am trying to combine. When I say combine, I mean that I want to add the tables together without merging or concatenating the data. sets; having each record in each data table as being unique. I have tried using the Power Query, but without no luck. I have a feeling that I will need to have code to copy and paste one table to the workbook, then copy and paste the second table to the next empty row, but not sure what to do.

Thank you,
Mack

 

[johnny51981]

Since you stated that they have the same headings in both...
Merging Queries in Power Query essentially allows you to bring data together horizontally.
Have you tried Append Query in Power Query? Since the headers are the same, it will list them vertically.

 

[Mack23]

That's what I thought but when I append the two queries that are individually pulling the source data from the two workbooks I only see the data from the first data table and nothing from the second. When I merge, It only shows the first table data only.

 

[johnny51981]

Now when you say "It only shows" are you still in the Query Editor, or have you loaded it to a table on an Excel Sheet? Probably a silly question, but just making sure.

 

[Mack23]

Both actually

 

[johnny51981]

Hmm. Well the Query Editor will only ever show the first 1000 records that make it through all of the steps.

I'm stumped without a sample set of data.

 

[Mack23]

Table 1:

Date	Daily Output	Allotment Month	Shift	Location
4/15/2022​	3022​	April	1st	Reading
4/15/2022​	2091​	April	1st	Reading
4/15/2022​	296​	April	1st	Reading
4/18/2022​	2516​	April	1st	Reading
4/18/2022​	3325​	April	1st	Reading
4/18/2022​	609​	April	2nd	Reading
4/18/2022​	1539​	April	1st	Reading
4/18/2022​	514​	April	1st	Reading
4/18/2022​	3632​	April	1st	Reading
4/18/2022​	1928​	April	2nd	Reading
4/19/2022​	3201​	April	1st	Reading
4/20/2022​	1469​	April	1st	Reading

Table 2:

Date	Daily Output	Allotment Month	Shift	Location
3/1/2022​	3009​	March	1 st	McKeesport
3/1/2022​	3010​	March	1 st	McKeesport
3/1/2022​	600​	March	1 st	McKeesport
3/1/2022​	1338​	March	1 st	McKeesport
3/1/2022​	3166​	March	1 st	McKeesport
3/2/2022​	778​	March	1 st	McKeesport
3/2/2022​	3614​	March	1 st	McKeesport
3/2/2022​	3357​	March	1 st	McKeesport
3/2/2022​	1400​	March	1 st	McKeesport
3/2/2022​	1066​	March	1 st	McKeesport
3/2/2022​	1140​	March	1 st	McKeesport
3/2/2022​	926​	March	1 st	McKeesport
3/2/2022​	2842​	March	1 st	McKeesport
3/3/2022​	4539​	March	1 st	McKeesport
3/3/2022​	730​	March	1 st	McKeesport
3/3/2022​	4296​	March	1 st	McKeesport
3/3/2022​	1260​	March	1 st	McKeesport
3/4/2022​	2299​	March	1 st	McKeesport
3/4/2022​	2626​	March	1 st	McKeesport
3/7/2022​	4888​	March	1 st	McKeesport
3/7/2022​	1505​	March	1 st	McKeesport
3/7/2022​	3178​	March	1 st	McKeesport
3/7/2022​	1820​	March	1 st	McKeesport
3/8/2022​	322​	March	1 st	McKeesport
3/8/2022​	1884​	March	1 st	McKeesport
3/8/2022​	564​	March	1 st	McKeesport
3/8/2022​	5880​	March	1 st	McKeesport
3/9/2022​	1210​	March	1 st	McKeesport
3/9/2022​	2412​	March	1 st	McKeesport
3/9/2022​	4452​	March	1 st	McKeesport

This is just a sample, but the one has about 15 lines the other table has about 140 lines.

 

[Mack23]

Is good that it is formatted as a table, correct. If not it would not be as easy for the query to connect and pull from.

 

[johnny51981]

It can be a table or it can be a sheet of information. Power Query should be able to pick it up no problem.

I've just done it on my side and all works OK. I just can't provide as an excel attachment.
So in a blank Workbook, enter Power Query Editor.
Add each Sheet from the Workbooks of Data.

Then simply Amend as New.
Close & Load.
All 3 will then load into your new previously blank Workbook, Table 1, Table 2, and the Amended Query as separate tabs.

 

[Mack23]

It did it. I changed something in the source pull though. When adding the sheets, before I was adding the data table as the source, this time I added the sheet from each work book, not the data table. Hopefully this will get me what I need. I will let you know if there is anything else on this matter.

Thank you very much!
Mack