# Stack data tables from different sources with PowerPivot



## bauer24 (Sep 18, 2012)

Has anyone come across a way to stack historical data with 'live' data?

So imagine if I have live data pulling from a database (Analysis Services) that shows 2012 sales data to date.  In a separate Excel file I have 2011's sales data, in the same structure.

I've seen online that a "union all" statement can be used to join two excel files in PowerPivot but has anyone stacked *database data* with _*Excel data*_ using a similar method?

I can post more info if it helps.

Thanks!!


----------



## JavierGuillen (Sep 18, 2012)

The short answer is no, you can't do stack them using a similar method.   You can, however, use both sources in one PowerPivot report.  Is your intention to stack them or can this requirement be ignored as long as the final report properly represents data from both sources?


----------



## bauer24 (Sep 19, 2012)

I'm hoping to make a pivot chart which shows sales over time.  So I imagined that I would need to stack both datasets on top of one another.  It's a real shame (and a massive drawback) if this isn't possible with PowerPivot


----------



## JavierGuillen (Sep 19, 2012)

Stacking tables is not always needed to create the kind of report you need.   By generating appropriate lookup tables, you can create a consolidated pivot chart with data from your relational database (or OLAP cube) as well as your excel file.

The following blog entry explains how to use two tables in a consolidated report, without the need for stacking them:

Merging data in PowerPivot « Javier Guillén


----------



## Fazza (Sep 20, 2012)

I don't know about Power Pivot but in earlier versions of Excel joining data from different sources should be do-able. Please try something like
	
	
	
	
	
	



```
SELECT fields
FROM `fully qualified reference to database`.[table]
UNION ALL
SELECT fields
FROM `qualified reference to Excel file`.[table]
```


----------



## bauer24 (Sep 25, 2012)

I found the trick is to transfer the cube data into an Excel sheet by using a "flattened pivot table"


----------

