Merging Tables with Different Columns and Layouts in PowerPivot
So I have not really worked in-depth with PowerPivot up to this point. I have played around with it but never really done anything substantial. However, I recently embarked on a pilot project for a client where PowerPivot was potentially the right solution for them. This client’s data resides in multiple systems, which is fairly normal now-a-days. The kicker for them was that most of their systems are hosted by outside providers and the options to get data out of those systems is limited. Some of the limitations are forced by the systems themselves while some are financial as the provider wants extra money for every change in the export format or for each export. So basically I am dealing with data that comes to the client in 8 or 9 spreadsheets, all with different columns and all with different column names. To top it off, not all the columns are consistent across the spreadsheets.