Data from multiple sources (current + archive)

[schbrownie]

How do I do this in PowerPivot?

I need to combine two data sets (one pulls from a static spreadsheet the other from a live access database) to run long time sales analysis. The "archive" data set is from a static spreadsheet(s) and has sales information from prior years. [e.g 2007 - 2010] the "live" data set pulls from an access database (which is updated daily) and contains current sales information (2011 - 2013 YTD).

the tables are very similar; except the history table (which was originally made from the live version) has a couple fewer columns as they were not added to the live data set until recently.

How do I combine the two data sets to create one seamless list of sales history?

thank you

p.s. if i choose to just sum both the archive table and the current table, i end up with twice the number of columns i need... the archive has blanks for current years and the live data set has blanks for prior years. while this does get me the data I need; it is ugly and un-presentable to my management.

 

[MD610]

PowerPivot can't really merge the data from 2 separate sources on it's own. This either needs to be handled in a database (Access or SQL Server) before importing to Power Pivot or with Power Query which is an Excel Add-in in the same MS Power BI family as Power Pivot. Power Query is for importing and shaping data from different sources for use in regular Excel and Power Pivot.