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

[dieffen]

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?

[Ken Puls]

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.

[dieffen]

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

[Ken Puls]

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,

[dieffen]

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

[Ken Puls]

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!