CODEWITHSUNDEEP
sqlexcelpowerpivotpowerqueryconsolidation
Brendan
Brendan

Reputation: 3

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

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.

Upvotes: 0

Views: 681

Answers (2)

Mike Honey
Mike Honey

Reputation: 15017

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

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$];

Upvotes: 0

Related Questions