Using GetPivotData or IF Statements

Question

Hey - 

I have been battling this for the past two days. I'm trying to refer to some data in a Pivot Table to bring into a table I've made on another tab. I understand I can do it by turning getpivotdata off and referring to the cells itself like usual, but I am trying to use getpivotdata or something more efficient so I can scale this. I've also tried IF statements but haven't had any luck. I'm sure it's user error. I've watched many YouTube videos, but can't seem to put my finger on what I'm doing wrong/what I should be doing. 

Just to clarify, all the data is dummy data, so there no issues with that. 

You'll notice in my table there are 4 fields that I am trying to pull in from my pivot table. 

Here is the file. Thanks!

Answer 1

Excel 2016 Power Query

With 3 common key fields, just a simple PQ Merge was needed.

More indirectly related databases might need a "Relationship" established in PP with DAX.

Reduced file size from 3000KB to 76KB by removing duplicates.

http://www.mediafire.com/file/bqt3931rsyjahn7/02_10_17a.xlsx

Answer 2

Share one database that you supposedly cannot fit

into the faux Table on tab "Table".

Please prune that file to less than 100KB.

PQ is just a tool to prepare data for PowerPivot.

Hope you got Excel 2016 Pro Plus and know DAX.

I won't be able to share the data from the database, but I created some dummy data based on the fields in the "Table" tab. I've put that table in the "Data" tab, and I've colored the associated fields pink in the "Table" tab. 

I have excel 2016, and I am familiar with DAX. Apparently not familiar enough with DAX though :) 

https://1drv.ms/x/s!AioeoEp2VHrTgbI_Q3qjEzPkNTe29w

Answer 3

Share one database that you supposedly cannot fit

into the faux Table on tab "Table".

Please prune that file to less than 100KB.

PQ is just a tool to prepare data for PowerPivot.

Hope you got Excel 2016 Pro Plus and know DAX.

Answer 4

Excel 2016 with Power Query (aka Get & Transform)

No formulas, no VBA macro.

Generate four new fields.

http://www.mediafire.com/file/6lzafank6mo6avb/02_10_17.xlsx

Wow. That was very helpful! I have quite a bit of experience using PQ, but I never knew about the pivot function capability. 

An additional question. So this is extremely helpful in making the pivot table reflect what I was trying to see. My additional question is how would I most efficiently get this data into a large table/dash? I'm connecting too many different data sources, and I've used PQ to merge most of my queries together. However, I have some data connections that I am unable to merge based on not having any unique identifiers. So I was planning on creating separate Pivot Tables on separate tabs, and just referencing them to fill in values in my table. That's where I originally thought of the idea of using getpivotdata to fill in the values in the cells I wanted in my table.

I've attached a table to show what I'm kind of talking about. I've made the fields that refer to the pivot table you made orange. As you can see, there are many different fields. That's where I'd be having the different pivot tables like I mentioned earlier. 

Here is the updated file

Answer 5

Excel 2016 with Power Query (aka Get & Transform)

No formulas, no VBA macro.

Generate four new fields.

http://www.mediafire.com/file/6lzafank6mo6avb/02_10_17.xlsx