Combining tables with different number of columns, some in common with same header names

Question

Hello. So I am working out of the query editor of a PowerPivot. I have two tables in the PowerPivot window, which I am editing with the query editor in the table properties tab. The tables have a different number of columns, some columns they have in common with each other, each contain unique columns. The columns they do share have identical header names. I would like to stack one table on top of the other while adding new columns to the original table.

For example:<p></p>
<table><i>TableA</i>
  <th>Col1</th><th>Col2</th><th>Col3</th>
  <tr><td>A</td><td>B</td><td>C</td>
    </tr>
  </table><p></p><table><i>TableB</i>
<th>Col1</th><th>Col2</th><th>Col4</th>
  <tr><td>D</td><td>E</td><td>F</td>
    </tr>
  </table><p></p>
<table><i>Combined</i>
  <th>Col1</th><th>Col2</th><th>Col3</th><th>Col4</th>
  <tr><td>A</td><td>B</td><td>C</td><td>-</td>
    </tr>
  <tr><td>D</td><td>E</td><td>-</td><td>F</td>
    </tr>
  <tr>
    
  </table>
<p></p>

Example Code:

SELECT tableA.col1, tableA.col2, tableA.col3, NULL AS col4
FROM [tableA$]
UNION
SELECT tableB.col1, tableB.col2, NULL AS col3, tableB.col4
FROM [tableB$];

I saw a post that used the NULL AS in order to compensate for the missing column(s) but once I add it in I get the "no columns detected" error message. In reality I'm working with dozens of sheets with columns ranging from 15 to over70, and rows ranging from 300. to 350k which is why I'm to using PowerPivot.

I imported my sheets to PowerPivot by creating a connection to worksheet.

P.S. I'm new to both PowerPivot and Query Editor. Thank you for any help in advance.

Mike Honey · Accepted Answer · 2016-07-05 23:09:46Z

1

I would use the Power Query Add-In for this. In Excel 2016, Power Query is included on the Data ribbon as Get & Transform.

Connect to your database and choose the 2 tables. Change the Load To defaults to Only Create Connection, and uncheck Add this data to the Data Model. This will create 2 Queries.

Then create a 3rd Query by right-clicking the 1st query in the Workbook Queries pane and choosing Append. Select the 2nd table and you are probably just about done. The Query Editor will preview the result and you can remove or rename columns as required.

For the 3rd Query, change the Load To setting to Only Create Connection, and check Add this data to the Data Model.

As well as removing the need to code and maintain complex SQL, Power Query adds a layer of insulation to your Data Model. You can freely include data from other sources eg files, web services - you are no longer limited to a single connection.

answered Jul 5, 2016 at 23:09

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brendan Over a year ago

Thanks! I was able to append the spreadsheets. One question however, will the connection also have the one million row limit as a normal spreadsheet? The reason I was trying to use PowerPivot (beside analyzing the data) was so It can all my data in the Data Model.

Mike Honey Over a year ago

No, as long as you dont chose Load To / Table, then it is limited only by normal Power Pivot limits i.e. available memory, capped at roughly 3GB if you are running 32-bit Office.

ScaisEdge · Accepted Answer · 2016-07-05 19:05:09Z

0

You should remove the alias from the second select

SELECT tableA.col1, tableA.col2, tableA.col3, NULL AS col4
FROM [tableA$]
UNION
SELECT tableB.col1, tableB.col2, NULL , tableB.col4
FROM [tableB$];

answered Jul 5, 2016 at 19:05

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Combining tables with different number of columns, some in common with same header names

Example Code:

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example Code:

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related