Pulling non-number data from a pivot table

Question

I am fairly new to pivot tables and am stuck on the getpivotdata function. I have a pivot table set up like the attached example, with vendor names, contract titles, contract numbers, expiration dates, and some $ info. I need to pull the expiration dates and contract numbers into a separate sheet, without the references breaking every time the pivot table gets rearranged. The data in the pivot table comes from an online database, not an excel table.

Is there a way to do this with getpivotdata? Or do I need some other sort of workaround? I have looked and looked online but not found anything that seems to help, as all the reference materials I've found only provide examples with numerical/sum column data. Any answers much appreciated, thanks in advance.

detlef_lewin · Answer

s_christel&nbsp;I offer you a Power Query solution. The pivot table is not needed anymore.Since your source data comes from an external database the Source step in PQ will be different.Load the source data and the additional data into the PQ editor as a connection only. The source data has to be grouped in the same way as the pivot table.Merge both tables as a new query and expand the two additional columns and load it into the worksheet-The additional table and the merged table get a helper column to check whether all rows are in the&nbsp; other table&nbsp;respectively.&nbsp;

detlef_lewin · Answer

s_christel&nbsp;Try another pivot table:Contract Number and&nbsp;Revised End Date in the rows area.&nbsp;

s_christel · Answer

Detlef_LewinDo you mean build a new pivot table using the first pivot table as a source?&nbsp; The fields you mention are already in the "rows" area.&nbsp; I'm not sure building a second pivot table will work either because I need to add data/columns for things that aren't in the original source data (and not calculated fields, either, but strings like dates).&nbsp; That's why I'm trying to pull the pivot table data into a regular table.

detlef_lewin · Answer

s_christel&nbsp;I'm not sure building a second pivot table will work either because I need to add data/columns for things that aren't in the original source data (and not calculated fields, either, but strings like dates).&nbsp;You didn't mention this in your first statement.Please provide a sample file that explains the whole scope of what you are trying to achieve.&nbsp;

s_christel · Answer

Detlef_Lewin&nbsp;See new upload.&nbsp; There is now a sheet that has a regular table like the one I am trying to make.&nbsp; You'll see it pulls in some info with getpivotdata, but I had to copy over the text/string data by hand, and the last two columns are entered by hand as well.&nbsp;What I'd like is for the text/string columns that are matched with the pivot table to be sourced from the pivot table in such a way that a) if that data (e.g. the contract expiration date) changes, it updates in the regular table too, and b) if NEW data is added to the pivot table (e.g. a new vendor, or a new contract under an existing vendor) it would add that to the regular table as well, just leaving the last two columns blank for me to input manually.&nbsp;I know that b) is probably a stretch.&nbsp; I'd be thrilled just to solve a).

Forum Discussion

Pulling non-number data from a pivot table

7 Replies

Resources