Stack Tables in Powerpivot

[roymunoz03]

I have a database with data from 2013 onwards, then I have some historical table with 2012 data, and finally I have some table with 2014 historical data. So it will be 3 tables with similar information and details, but different time horizon. I am looking a way to combine all of those in Powerpivot, but in a way that it becomes one single master table. How can I do that? Please help me this should be easy to do!!!

 

[scottsen]

Your two best choices:
1. Handle it in SQL. (eg: UNION ALL)
2. Handle it with Power Query.

There is no built in way for Power Pivot to "append" to a table.

 

[Tianbas]

another way would be to create a new time table with all dates from 2012-2014. You can build relationship between this time table and your 3 data tables and add some calculated calumns with related() to pull all the data in your "stacked" table.

 

[DWig]

Posted on another thread. I solved a similar issue using UNION.

SELECT  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,SUM(GLSummary.Debit) AS Debit
  ,GLSummary.Project_Key
  ,SUM(GLSummary.Credit) AS Credit
,Max(FinancialPeriod.FinancialPeriod) AS FinancialPeriod
 ,Max(FinancialPeriod.FinancialPeriod_Key) AS FinancialPeriod_Key
FROM
  FinancialPeriod
  INNER JOIN GLSummary
    ON FinancialPeriod.FinancialPeriod_Key = GLSummary.FinancialPeriod_Key
WHERE
(FinancialPeriod.FinancialPeriod)<='12/31/2011'
GROUP BY
  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,GLSummary.Project_Key


UNION


SELECT
  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,SUM(GLSummary.Debit) AS Debit
  ,GLSummary.Project_Key
  ,SUM(GLSummary.Credit) AS Credit
,(FinancialPeriod.FinancialPeriod) AS FinancialPeriod
 ,(FinancialPeriod.FinancialPeriod_Key) AS FinancialPeriod_Key
FROM
  FinancialPeriod
  INNER JOIN GLSummary
    ON FinancialPeriod.FinancialPeriod_Key = GLSummary.FinancialPeriod_Key
WHERE
(FinancialPeriod.FinancialPeriod)>='1/1/2012'
GROUP BY
  GLSummary.Company_Key
  ,GLSummary.GLAccount_Key
  ,GLSummary.Project_Key
,FinancialPeriod.FinancialPeriod
,FinancialPeriod.FinancialPeriod_Key

 

[Fazza]

It is as simple as scottsen originally replied. UNION ALL. (In this instance, UNION should be OK as data is from different years, but safer to use UNION ALL.)

SELECT *
FROM table_one
UNION ALL
SELECT *
FROM table_two
UNION ALL
SELECT *
FROM table_three