Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Stack data tables from different sources with PowerPivot

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Stack data tables from different sources with PowerPivot


    Stack data tables from different sources with PowerPivot
    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!!

  2. #2
    Board Regular
    Join Date
    Aug 2012
    Location
    Charlotte, NC
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stack data tables from different sources with PowerPivot

    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?

  3. #3
    New Member
    Join Date
    Jan 2009
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stack data tables from different sources with PowerPivot

    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

  4. #4
    Board Regular
    Join Date
    Aug 2012
    Location
    Charlotte, NC
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stack data tables from different sources with PowerPivot

    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 by JavierGuillen; Sep 19th, 2012 at 07:33 AM.

  5. #5
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,982
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Stack data tables from different sources with PowerPivot

    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]

  6. #6
    New Member
    Join Date
    Jan 2009
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stack data tables from different sources with PowerPivot

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •