Merging Tables in one Database

[kaushikjo]

Hi!

I have around 20 different tables with the same field structure. I want to merge them all into 1 table (single database). I also want the single database to dynamically change if any of the 20 tables change. Is this possible using power pivot.
If power pivot does not help, I am finally thinking of creating 20 different pivots and calling for desired data using countifs in a single summary sheet.


kaushik

 

[Tycho01]

Yeah, this is pretty much what PowerPivot is intended for. It sounds like you may also be interested in automating the PowerPivot refresh using VBA (link).

 

[kaushikjo]

Hi!

I have around 20 different tables with the same field structure. I want to merge them all into 1 table (single database). I also want the single database to dynamically change if any of the 20 tables change. Is this possible using power pivot.
If power pivot does not help, I am finally thinking of creating 20 different pivots and calling for desired data using countifs in a single summary sheet.


kaushik

Thanks a lot! I have never used VBA scripts, just aware of some basics of Power Pivot...linking 2 tables with a common identifier. Hey but.... I will certainly check out the link you gave for some more stuff....do you have any advise on going about enhancing power pivot knowledge....I am a bit skeptical to use it on some live databases i have. Thanks again! Have nice day!

 

[Tycho01]

Hey,
If you wanna experiment more for practice, I'd recommend just getting a local copy of your data (if it's a file just copy it, if a DB just load the data) so you can try to improve without being able to harm the original. Chances are you will find ways to improve though!
I got started on both PowerPivot and VBA through books I found online; 'Practical PowerPivot & DAX Formula for Excel 2010' for PowerPivot, 'Excel VBA Programming for Dummies' for VBA. Seems like a chore, but not as bad anymore if you just skip all but the formulas (unless it's getting difficult). Might work for you too; the latter book is more understandable than recorded code for sure.

 

[kaushikjo]

Hey,
If you wanna experiment more for practice, I'd recommend just getting a local copy of your data (if it's a file just copy it, if a DB just load the data) so you can try to improve without being able to harm the original. Chances are you will find ways to improve though!
I got started on both PowerPivot and VBA through books I found online; 'Practical PowerPivot & DAX Formula for Excel 2010' for PowerPivot, 'Excel VBA Programming for Dummies' for VBA. Seems like a chore, but not as bad anymore if you just skip all but the formulas (unless it's getting difficult). Might work for you too; the latter book is more understandable than recorded code for sure.

Yes - I agree. I had tried creating dummy files, but due to paucity of time wanted to straight go to the live ones. I am buying a book on line right away.....and thanks for the tip on focus on formulas...yeah its quite a chore to learn VBA, I did try my bit, it needs patience and time, I thought I forgot easily as I had no application in my work or live projects! Well thanks so much for sharing your perspective! sure does help and give courage too!

 

[78chris]

Hello

If your tables are in Excel or a database, you can create a union query so to get only one single table for using it in PowerPivot.
You have an example on Contextures site

 

[Art15651]

78chris - thanks for the heads up on the Contextures example. It was exactly what I was looking for and has made me very happy today!