Pulling non-number data from a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-1554432%22%20slang%3D%22en-US%22%3EPulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554432%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20fairly%20new%20to%20pivot%20tables%20and%20am%20stuck%20on%20the%20getpivotdata%20function.%26nbsp%3B%20I%20have%20a%20pivot%20table%20set%20up%20like%20the%20attached%20example%2C%20with%20vendor%20names%2C%20contract%20titles%2C%20contract%20numbers%2C%20expiration%20dates%2C%20and%20some%20%24%20info.%26nbsp%3B%20I%20need%20to%20pull%20the%20expiration%20dates%20and%20contract%20numbers%20into%20a%20separate%20sheet%2C%20without%20the%20references%20breaking%20every%20time%20the%20pivot%20table%20gets%20rearranged.%26nbsp%3B%20The%20data%20in%20the%20pivot%20table%20comes%20from%20an%20online%20database%2C%20not%20an%20excel%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%20with%20getpivotdata%3F%26nbsp%3B%20Or%20do%20I%20need%20some%20other%20sort%20of%20workaround%3F%26nbsp%3B%20I%20have%20looked%20and%20looked%20online%20but%20not%20found%20anything%20that%20seems%20to%20help%2C%20as%20all%20the%20reference%20materials%20I've%20found%20only%20provide%20examples%20with%20numerical%2Fsum%20column%20data.%26nbsp%3B%20Any%20answers%20much%20appreciated%2C%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1554432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554937%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744145%22%20target%3D%22_blank%22%3E%40s_christel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20another%20pivot%20table%3A%3C%2FP%3E%3CP%3EContract%20Number%20and%26nbsp%3BRevised%20End%20Date%20in%20the%20rows%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1556399%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1556399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3EDo%20you%20mean%20build%20a%20new%20pivot%20table%20using%20the%20first%20pivot%20table%20as%20a%20source%3F%26nbsp%3B%20The%20fields%20you%20mention%20are%20already%20in%20the%20%22rows%22%20area.%26nbsp%3B%20I'm%20not%20sure%20building%20a%20second%20pivot%20table%20will%20work%20either%20because%20I%20need%20to%20add%20data%2Fcolumns%20for%20things%20that%20aren't%20in%20the%20original%20source%20data%20(and%20not%20calculated%20fields%2C%20either%2C%20but%20strings%20like%20dates).%26nbsp%3B%20That's%20why%20I'm%20trying%20to%20pull%20the%20pivot%20table%20data%20into%20a%20regular%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1556707%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1556707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744145%22%20target%3D%22_blank%22%3E%40s_christel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3EI'm%20not%20sure%20building%20a%20second%20pivot%20table%20will%20work%20either%20because%20I%20need%20to%20add%20data%2Fcolumns%20for%20things%20that%20aren't%20in%20the%20original%20source%20data%20(and%20not%20calculated%20fields%2C%20either%2C%20but%20strings%20like%20dates).%26nbsp%3B%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EYou%20didn't%20mention%20this%20in%20your%20first%20statement.%3C%2FP%3E%3CP%3EPlease%20provide%20a%20sample%20file%20that%20explains%20the%20whole%20scope%20of%20what%20you%20are%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1556977%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1556977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20new%20upload.%26nbsp%3B%20There%20is%20now%20a%20sheet%20that%20has%20a%20regular%20table%20like%20the%20one%20I%20am%20trying%20to%20make.%26nbsp%3B%20You'll%20see%20it%20pulls%20in%20some%20info%20with%20getpivotdata%2C%20but%20I%20had%20to%20copy%20over%20the%20text%2Fstring%20data%20by%20hand%2C%20and%20the%20last%20two%20columns%20are%20entered%20by%20hand%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'd%20like%20is%20for%20the%20text%2Fstring%20columns%20that%20are%20matched%20with%20the%20pivot%20table%20to%20be%20sourced%20from%20the%20pivot%20table%20in%20such%20a%20way%20that%20a)%20if%20that%20data%20(e.g.%20the%20contract%20expiration%20date)%20changes%2C%20it%20updates%20in%20the%20regular%20table%20too%2C%20and%20b)%20if%20NEW%20data%20is%20added%20to%20the%20pivot%20table%20(e.g.%20a%20new%20vendor%2C%20or%20a%20new%20contract%20under%20an%20existing%20vendor)%20it%20would%20add%20that%20to%20the%20regular%20table%20as%20well%2C%20just%20leaving%20the%20last%20two%20columns%20blank%20for%20me%20to%20input%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20b)%20is%20probably%20a%20stretch.%26nbsp%3B%20I'd%20be%20thrilled%20just%20to%20solve%20a).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557088%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744145%22%20target%3D%22_blank%22%3E%40s_christel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20offer%20you%20a%20Power%20Query%20solution.%20The%20pivot%20table%20is%20not%20needed%20anymore.%3C%2FP%3E%3CP%3ESince%20your%20source%20data%20comes%20from%20an%20external%20database%20the%20Source%20step%20in%20PQ%20will%20be%20different.%3C%2FP%3E%3CP%3ELoad%20the%20source%20data%20and%20the%20additional%20data%20into%20the%20PQ%20editor%20as%20a%20connection%20only.%20The%20source%20data%20has%20to%20be%20grouped%20in%20the%20same%20way%20as%20the%20pivot%20table.%3C%2FP%3E%3CP%3EMerge%20both%20tables%20as%20a%20new%20query%20and%20expand%20the%20two%20additional%20columns%20and%20load%20it%20into%20the%20worksheet-%3C%2FP%3E%3CP%3EThe%20additional%20table%20and%20the%20merged%20table%20get%20a%20helper%20column%20to%20check%20whether%20all%20rows%20are%20in%20the%26nbsp%3B%20other%20table%26nbsp%3Brespectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557152%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557152%22%20slang%3D%22en-US%22%3EPQ%20is%20the%20best.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557270%22%20slang%3D%22en-US%22%3ERe%3A%20Pulling%20non-number%20data%20from%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557270%22%20slang%3D%22en-US%22%3EI%20got%20as%20far%20as%20opening%20the%20data%20in%20power%20query%2C%20only%20to%20find%20I%20don't%20have%20permissions%20to%20use%20the%20connection%20string.%20The%20database%20and%20pivot%20table%20are%20managed%20by%20another%20department.%20Perhaps%20they'll%20give%20me%20permission%2C%20if%20not%2C%20I'm%20going%20to%20write%20this%20one%20off%20as%20a%20case%20of%20%22do%20it%20by%20hand%22.%20Thanks%20for%20your%20help%2C%20DetLef!%3C%2FLINGO-BODY%3E
New Contributor

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.

7 Replies

@s_christel 

Try another pivot table:

Contract Number and Revised End Date in the rows area.

 

@Detlef LewinDo you mean build a new pivot table using the first pivot table as a source?  The fields you mention are already in the "rows" area.  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).  That's why I'm trying to pull the pivot table data into a regular table.

@s_christel 


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). 

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.

 

@Detlef Lewin 

See new upload.  There is now a sheet that has a regular table like the one I am trying to make.  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.

 

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.

 

I know that b) is probably a stretch.  I'd be thrilled just to solve a).

@s_christel 

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  other table respectively.

 

PQ is the best.
I got as far as opening the data in power query, only to find I don't have permissions to use the connection string. The database and pivot table are managed by another department. Perhaps they'll give me permission, if not, I'm going to write this one off as a case of "do it by hand". Thanks for your help, DetLef!