Results 1 to 6 of 6

Thread: How to pull field from related lookup table and use in Value area of Powerpivot

  1. #1

    How to pull field from related lookup table and use in Value area of Powerpivot



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

    To simplify, I have a data table with a code field and then many other numeric field to use for various calculations. It is related to a lookup table that has the code, a code desc, and a code weight value. In my powerpivot table I've pulled in the Code and Code Desc from the lookup table in the Rows section and some numeric data from the data table that sums and averages nicely. I only get rows of the code and desc where there are records in the data table. BUT when I put the Weight field from the lookup table into the Values section of the pivot table I then get a row for every value in the lookup table even if that code is not in the data table but the Value area for those Codes is null. How do I pull in the weights from the lookup table into the Values area?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    General rule of thumb is "don't".

    I would seriously look at restructuring your data, as your lookup tables should only ever be used on row, column or filter fields, or on slicers/timelines, and NEVER on values fields. However, should you have no away around this issue, you could do this:

    Go to your "fact" table and write an =RELATED(lookuptable[column]) formula

    This will work to "flatten" your table, bringing the related lookup values back to your fact table, but it costs memory and is not a best practice. It would be better to use Power Query to pre-shape the data before loading it in to Power Pivot.
    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: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
    Ok. Thanks Ken. I'm trying to do everything in PQ (put my "M is for (Data) Monkey" purchase to use )). Historically I've done this in Access by joining the tables. So pre-shape using a Merge? Do I have to pull in all the fields (which includes the Weight field) of the lookup table or can I pull in just the weight value? I was trying to practice good technique by creating a lookup table to get the code descriptions and keep my data table as narrow and small as possible.

    = Table.NestedJoin(#"Changed Type3",{"DRG Code"},lkupMSDRGLookup,{"MS-DRG"},"NewColumn",JoinKind.LeftOuter)


    Thanks for your help,
    Eric

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    My rule of thumb is to try and get all the numeric values (and text of occasion) that I want to return into the values area of the pivot into a flattened table. So in this case, not knowing your data, I'd probably try to get the weight, quantity, cost and other numeric values into one table. I'd have another column that was item name, which linked out to the items table as I'd expect the item to be sold (or consumed) many times.

    Other properties of the item (like classification, etc) would live in the items table.

    Does that makes sense?

    If you can upload a small sample of what your data looks like (fake it with data that represents the same patterns of the original), I'm sure we could help determine the optimal solution. What would be interesting is to see a couple of excel tables that represent what the raw data looks like, and what kind of a pivot you'd like to get out on the other end.

    Cheers,
    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: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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.

  5. #5
    Thanks. Attached is the main data table and two lookup tables. As you can see, the data table is quite wide and has a lot of codes and descriptions fields that I have created lookup tables for to allow me to remove the descriptions out of the data table. For a few tables (like the two in the attached) there are some numeric weights that I want to pull in not as a row but in the Value area of the pivot.


    Thanks,
    Eric
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hey Eric,

    Sorry, I'm bogged down on client projects.

    The quick answer is that the RELATED function will get you where you need to go for now. I'll try and look at this over the next few days to give you a better answer, but I need to study what's built up here to figure it out, and right now I'm a bit short on time.

    If I don't get back to you within a few days here, please don't hesitate to ping me on this thread.

    Cheers!
    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: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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.

Posting Permissions

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