Stack data tables from different sources with PowerPivot

bauer24

New Member
Joined
Jan 12, 2009
Messages
23
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!!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
Code:
SELECT fields
FROM `fully qualified reference to database`.[table]
UNION ALL
SELECT fields
FROM `qualified reference to Excel file`.[table]
 
Upvote 0
I found the trick is to transfer the cube data into an Excel sheet by using a "flattened pivot table"
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top