Results 1 to 10 of 10

Thread: help with powerpivoting from multiple tables

  1. #1

    help with powerpivoting from multiple tables

    Register for a FREE account, and/
    or Log in to avoid these ads!

    any help on this is much appreciated:
    how do I get the attached pivot table to work? I'm trying to get the '2013 estabs' column pull in same data point as in the '2012 estabs' . Now it is giving the total for the entire source column in the source table. In the powerpivot window, i made a primary key column and linked the two tables but it's not working. hope you see what i mean. the actual tables are 80,000+ rows each and want to have about 10 tables of that size linked.

    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    Hi Fadi,

    The easiest way to deal with the scenario you are facing is actually to copy all of your 2013 data into your 2012 table, refresh the model, and drag the Year onto the columns. That should sort it.

    In the grander scheme though, the challenge you are having is that your data sources need to be taken back into a normalized form. To make PowerPivot truly sing, you need to have a table that includes your columns A:M, as well as a Calendar table. You'd link both the calendar and the first table to your individual year's data and you should be able to report on what you need. As your data is currently set up though, it's going to cause you grief, I'm afraid.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

  4. #4
    Ok, thanks much. So answer is Copy and Past Append because of lack of normalization.
    Though interesting there's no Append option when Get External Data From Text File in the PowerPivot window - It creates new table with each text file import which then needs to be copied and past Append to make one consolidated table.

  5. #5
    Paste Append is just one method.
    Another way is to create a relationship between the two tables
    and tables that contain their unique values, such as dates, as Ken mentioned.
    See PowerPivot2 in the same link.

  6. #6
    Thank you much. What a great exercise on database normalization which I needed. I recreated my workbook and followed your example on the various lookup tables and created the relationships but I didn't create a 'both' table and the 'Estabs' measure, and it is not working. Why do I need the 'both' table which seems to be the two tables appended if I have the relationships? I thought it was either combine the tables or create the relationships?

  7. #7
    Divorced "Relationship" method from "Paste Append" method.
    Start fresh. Don't try patching.

  8. #8
    I'm getting it.. I tried doing it without this Estab12 and Estab13 measures and it doesn't work - something about doesn't know the value of the field in this context, hence you need the sum operator on the individual fields in the tables. makes sense. once last question, what do you call or what's the logic behind the Estab measure that joins them together with the "+" in table1 (along with the fips field)?

  9. #9
    The area_fips and area_title fields are redundant. Use either of both.
    "+" means sum. Play with the Measures.
    Here is another version using concatenate.
    "Cat" can also be generated as a Calculated Column.

  10. #10
    thanks again. and am only posting so i get to 5 post count as i need to submit another question in another thread with link to a file.

Posting Permissions

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