Import Excel sheets and combine with SQL query editor?

[pete234]

I'm probably misunderstanding some information I came across the other day.

For a while now, I refresh 7 tables in a powerpivot workbook that are sourced from Excel sourced from a CRM site.
This will change soon but I thought to practice some SQL and write a query and combine these sheets on the import,
so I'd have one big table with report names instead of 7 reports to refresh.
Data is similar but not exactly UNIION ready from what I know.

I was on contexture reading about this but I can't see how to change a table to and SQL source like they show here

PowerPivot from Identical Structure Excel Files

Is what I want to do possible? I don't want to import first, I want to import it the way I specify in the query.
Or is this only good with sql server? The "switch to" field is greyed out on my tables when I click Table Properties.

 

[Jacob Barnett]

Pete, the trick is to follow the instructions to the letter - just importing the excel table will not give you access to the 'query editor' drop down. You have to create the connection through the connection dialogue which must 'trick' PowerPivot into thinking its something more sophisticated than an excel connection. Once that has been imported you can get access to the SQL editor and do the UNION etc.

The queries you union together don't have to be identical they just have to produce an output with the same number of columns that have the same/compatible data types.

Jacob

 

[pete234]

Pete, the trick is to follow the instructions to the letter - just importing the excel table will not give you access to the 'query editor' drop down. You have to create the connection through the connection dialogue which must 'trick' PowerPivot into thinking its something more sophisticated than an excel connection. Once that has been imported you can get access to the SQL editor and do the UNION etc.

The queries you union together don't have to be identical they just have to produce an output with the same number of columns that have the same/compatible data types.

Jacob

Ok I will definitely reread it, I kept thinking I would wind up with 7 more tables but maybe it's just 1 that I can then trick into seeing the others? I hope so, that would be great.