# Adding Power Pivot Data to the Data Model?



## Maggie Barr (Apr 11, 2019)

Hello and thank you in advance if you can help,
I am using MS Office 2019 on a PC.  I am somewhat new to Power Query and Power Pivot, and I am trying to analyze a data set in a way that as much of it as possible is "automatic".  I currently have to run multiple pivot tables and bring information from them, by hand, into a final summary table.  I would like to add my pivot tables to the data model so that I could potentially build on the model, and thus be able to bring data from those directly into a final summary.  When I go to insert a pivot table, use this workbook's Data Model, Existing Worksheet, the little box for Add this data to the Data Model is grayed out.  Does anyone know why that is and how I can get the data into the model.  I have all my pivot tables built, and if there were a way to select the table and add it, that would be great, but, if not, I can recreate them, it is just not giving me the option.  My pivot tables do have more than one field as a header (meaning two rows), so I do not know if that will pose a problem.
Any help would be appreciated,
Thank you,
Maggie


----------



## theBardd (Apr 11, 2019)

You should add the source of the pivots to the datamodel, not the pivots themselves. If necessary, join them in Power Pivot if they are different structures, or merge them in Power Query if they are the same.


----------



## Maggie Barr (Apr 12, 2019)

theBardd,
Thank you for getting back to me.  The data the pivot is based on is in the data model already.  Now it could be that the formulas needed to create measures to come up with some of the information I need, without first running pivots, are beyond me, but I am just not able to see how I can create one pivot that does what several pivots do in regards to the analyses I have to do.  If I could create a dataset off a pivot, I could then create the measures I need from those summaries to populate some of the fields in the Regional Summary table I have developed.  For instance, in one case I need to run a pivot, and then I copy the pivot to a sheet and run a macro that essentially behaves like an unpivot (still trying to learn this one), and then apply filters to the results and run a count function of the remainder.  If I could add the pivot to the data model, I could then interact with that data again without breaking the chain, so to speak.  I had searched online, and from what I understand, there is no way to link data from a pivot directly to a data model, I only found copy pivot, paste, and add to model as the option.  That is unfortunate, because having to copy the data, create a sheet from it, is a break in the chain of automaticity in the analyses that I am trying to create.  Not a deal breaker, just disappointing.  I will look into syntax for measures, but most of the examples I find are not what I am looking for, like to how to group a large dataset by multiple columns thus creating a count function of what is counted within those groups.  Pivots are the only way I have found to do it.  I have species as rows, and two column headers of region and block type, with the count of species as values, and from that I run a count function of the columns to get how many species are reported for that block type within that region, plus I bring those counts from the values into a final table and the counts from the count function I run into a header of the table with specific summary analyses.  Oh bother, sorry to be rambling, and probably none of this makes sense, but, in summary, there is no way to link a pivot to the data model directly, but I want to thank for trying to help.
Best Wishes,
Maggie


----------



## theBardd (Apr 12, 2019)

I'm sorry, but you just seem to be raising general points which are very hard to discuss without knowing the data or what your objectives are.
In addition, much of what you said makes little sense to me -


Maggie Barr said:


> The data the pivot is based on is in the data model already.



If this is the case, if it is complete and robust data, you are good to go. What you do depends upon the analyses, 



Maggie Barr said:


> Now it could be that the formulas needed to create measures to come up with some of the information I need, without first running pivots, are beyond me, but I am just not able to see how I can create one pivot that does what several pivots do in regards to the analyses I have to do.


Why would you want one pivot to do what several existing pivots do? If those several pivots are looking at differing vies of the data that would seem the correct way to go to me.  If you have those pivots, what is wrong with them?



Maggie Barr said:


> If I could create a dataset off a pivot, I could then create the measures I need from those summaries to populate some of the fields in the Regional Summary table I have developed. For instance, in one case I need to run a pivot, and then I copy the pivot to a sheet and run a macro that essentially behaves like an unpivot (still trying to learn this one), and then apply filters to the results and run a count function of the remainder.


Power Query does an unpivot easily, but unless you have a crosstab dataset, it doesn't seem that you need to do that. Also, Power Query can be used to add extra data if that is required, not measures as such, although measures might acheive the same objective - as I said, it depends upon wht you are trying to do.



Maggie Barr said:


> If I could add the pivot to the data model, I could then interact with that data again without breaking the chain, so to speak.


No idea what that means.



Maggie Barr said:


> I had searched online, and from what I understand, there is no way to link data from a pivot directly to a data model, I only found copy pivot, paste, and add to model as the option. That is unfortunate, because having to copy the data, create a sheet from it, is a break in the chain of automaticity in the analyses that I am trying to create. Not a deal breaker, just disappointing.


As I keep saying, I fail to see why you would need to do this. If you have the data, work on that.



Maggie Barr said:


> I will look into syntax for measures, but most of the examples I find are not what I am looking for, like to how to group a large dataset by multiple columns thus creating a count function of what is counted within those groups.


That sounds the sort of thing that DAX should be able to manage, but so can Power Query. How would grabbing the pivot data help there?



Maggie Barr said:


> Pivots are the only way I have found to do it. I have species as rows, and two column headers of region and block type, with the count of species as values, and from that I run a count function of the columns to get how many species are reported for that block type within that region, plus I bring those counts from the values into a final table and the counts from the count function I run into a header of the table with specific summary analyses.


You can do that in POwer Query, albeit it is not trivial, but why bother when the pivot does it so much better?


----------



## macfuller (Apr 13, 2019)

I may be misunderstanding your original post, but are you trying to add data in pivot tables that were sourced from the data model _back_ into the data model?  If so, that can't be done, much as all of us would like it to.  The data model can only add data sourced from outside the model.

If your original post is describing something else then sorry I misunderstood.


----------



## macfuller (Apr 13, 2019)

This is probably an imperfect analogy, but think of the data model as  one of those glass boxes in the movies where the deadly virus is kept  inside and you can only handle it with the rubber gloves sticking into  the box (waldoes).  Let's use Legos inside the box instead since it's  less scary :wink:.   You have 4 queries Green, Grey, Blue, and Red that put 568 total Legos  into the box. DAX is the rubber gloves - you can use the Legos to build  a house, car, or Millenium Falcon, but whether you use them in a  measure or not there are always 568 pieces in there that cannot be added  to or taken away.  Queries only put Legos into the box, they can't take  any away without taking them all away by altering or deleting the  query.  And they do all operations outside the box, only putting the  finished number of Legos into the box.

Your Green query puts 51 pieces (say 51 weeks of data) into the data  model.  You want to split Green into Big Green and Little Green.  Big  Green already put 51 pieces into the data model.  It's already there,  visible right on the other side of the glass box.  You want Little Green  to add the 52nd piece to the Legos already in there.  You can see the  51 pieces right there - no way I should have to run Big Green again!  So you tell Little Green just to stick the 52nd  Lego to the top of the 51 that are already there.  But Little Green  tells you it can't find the other 51 because they're not outside the box  - there's nothing to stick it to unless you re-run the Big Green query  to re-create the other 51.

There are probably a ton of reasons Power Query works this way... if  your Big Green query ran a segment where you wanted to sum and group by  the latest date in a month your prior 51 weeks would be wrong for  December because you'd need the 52nd week to make your month totals  right.  There's no way PQ could figure out how to untangle that logic.   So your attempt to use the data model tables is blocked since PQ can't  take data from there.

It's agonizing to have to run the full data queries when all you want to  do is append a new and smaller set of data and there's no data  transformation necessary, but that's just the way it is.

And if my analogy is wrong I'm delighted to have other readers correct it!


----------



## Maggie Barr (Apr 13, 2019)

macfuller,
Thank you, that is what I would have liked to do, and it is unfortunate that I can't, but I know how to do what I need to without it, it just would have been nice.
Best Wishes,
Maggie


----------



## Maggie Barr (Apr 13, 2019)

theBardd,
I am sorry for that confusing ramble, and I regretted the confusion after I posted it.  I cannot share my data, but I can share what the report I am producing looks like.  It is very difficult to discuss fully just what goes into the report and how I get the data to populate each cell and/or column within the report.  I am not an excel guru, but I do my best.  I am just trying to get as close to a refresh and voilÃ*, there is the report type method as possible tp minimize the hand work.  Between raw data of a couple of million records, spatially joined to ArcMap, brought into Access to manipulate, test, and filter, then brought in as a link to Power Query as a data source and running pivots from there, I think I am making a lot of progress.  I just haven't learned to write DAX measures yet, or know if it can produce some of the things I need (big learning curve).  I have been able to create some reference queries to the full data that can produce some of the things I need, but getting it all back in to populate the format of my report is difficult.  Below is a link to an excel workbook with a few sheets.  I put this there, not so much that I expect you to dive into it, but mostly to try to, hopefully, redeem myself for sounding so utterly confused/ing.
Thanks,
Maggie
https://app.box.com/s/szfp43a1vbf7w5v6poh9w50pyp65c08b


----------



## macfuller (Apr 13, 2019)

Wouldn't the Audubon society have some tech resources that could help you?  I would think what you're trying to do would be a delight for them to help you!


----------



## theBardd (Apr 13, 2019)

Looking at your data, it seems very straightforward. Your formula is a little different than I would have used, but it works (I must admit to be intrigued that you have sightings in the future!). I still am not clear what your objective is, why you want to include pivot data into your data model. That formula is easily reproduced in Power Query. Can you elaborate on what you are trying to do, in relation to this dataset.


----------



## Maggie Barr (Apr 11, 2019)

Hello and thank you in advance if you can help,
I am using MS Office 2019 on a PC.  I am somewhat new to Power Query and Power Pivot, and I am trying to analyze a data set in a way that as much of it as possible is "automatic".  I currently have to run multiple pivot tables and bring information from them, by hand, into a final summary table.  I would like to add my pivot tables to the data model so that I could potentially build on the model, and thus be able to bring data from those directly into a final summary.  When I go to insert a pivot table, use this workbook's Data Model, Existing Worksheet, the little box for Add this data to the Data Model is grayed out.  Does anyone know why that is and how I can get the data into the model.  I have all my pivot tables built, and if there were a way to select the table and add it, that would be great, but, if not, I can recreate them, it is just not giving me the option.  My pivot tables do have more than one field as a header (meaning two rows), so I do not know if that will pose a problem.
Any help would be appreciated,
Thank you,
Maggie


----------



## Maggie Barr (Apr 13, 2019)

macfuller,
The project involves a large group of cooperating organizations, State and Non-profits, that are collaborating on various aspects of this.  I am just one person working for a small non-profit that is assisting in handling certain aspects of the data.  With everyone knowing who plays what roles and why, which is beyond me, it is on our plate, so I am not asking questions, just proceeding.  It is one of our roles to assist with the regional summaries for regional coordinators and to bring the processed summaries back into ArcMap for visual displays and monitoring progress.  In the conservation world, everyone's plate is full, so while there may be data specialists who could help somewhere, their plates may be full with other tasks.  I have made great progress in streamlining, but I am always trying to discover more.  With infinite time and resources, this would be easier, but with time constraints, I learn a little more between each summary where I can streamline a little more, but then move on to get the results.  Learning on the job per se.  This is a four year project, with monthly downloads and various analyses depending on the season, with the data set getting larger every month, so perhaps I will get to the point where it could be a "click refresh" and there you have it, but I am not there yet. Thanks for the advice/input, and I loved the analogy you provided!
Best Wishes,
Maggie


----------



## Maggie Barr (Apr 13, 2019)

Oh Bother!
I clicked to get the wrong link, that was another forum question.  SOOOO sorry!
https://app.box.com/s/e17s0dfrouk8w4t147499q37vivzmzve
Maggie


----------



## theBardd (Apr 13, 2019)

Are you saying you create that report from the data fields and the locations tables? If so, what do you have already, and how, and what do you still need to automate?


----------



## Maggie Barr (Apr 13, 2019)

theBardd,
I create that report, but much of it is manual, filling columns from different pivots, and in some cases, taking counts from formulas I manually enter for columns within some pivots to populate cells in the report header.  Messy sounding, I know, but I have not figured a way to link those cells to values in pivot tables.  Then, in some cases, I can automate some of the cell data in the report header with formulas based on the data within the report sheet.  As I said, I can generate what I need, but I am having difficulty in learning how to create that report so it can just be "refreshed".  Perhaps there are report builder tools that I know nothing about, which is likely the case, but I am learning as I go.  I do not want to waste your time, and I truly appreciate your interest and effort in trying to help, but I am not sure that, without being able to share the data, I can provide enough clarity for you to really explain it enough.  I will keep "playing" as things progress, and hopefully I will get there.
Thank you,
Maggie


----------



## theBardd (Apr 13, 2019)

Do you need to see every Site in the report, or would it be okay to have a slicer with sites on it and just view a selected site at a time?


What I 'm thinking I cube formule, but if we were to show all species against every site, I think I might get very slow, but if we were jut to show one and use th data model to handle switching between sites that might be more manageable. If you needed them all it would be a trivial task to select one at a time on a worker sheet and copy/paste values to a final report sheet.


Are those two tables examples of what you get or are there more. I would need a site table ad maybe more, some I could get from that data, such as sites, but are there others needed?


----------



## theBardd (Apr 13, 2019)

Thinking about this some more, how about just using VBA to populate the final report from your pivot results?


----------



## Maggie Barr (Apr 13, 2019)

theBarrd,
You are so kind to keep exploring this with me.  The "data" sheet is a snap of some made up data, not the full data.  I had watched a tutorial on pivot techniques by Chris Dutton, which showed me a lot about slicers and filters for interactive report development. But I have yet to attempt to take my report to that interactive level. Upon my searching online for how to get pivot data into a "model" in attempts to create links in a "form" to its results, I stumbled across Cube concepts, and even for fun, copied one of my pivots to another sheet, and went into olap tools and converted to formulas to investigate...oh bother, I realized that perhaps I will have to learn that one another time.  If you know of any good tutorials on such, I would greatly appreciate the tip.  
Thanks again,
Maggie


----------



## theBardd (Apr 14, 2019)

Maggie Barr said:


> . But I have yet to attempt to take my report to that interactive level. Upon my searching online for how to get pivot data into a "model" in attempts to create links in a "form" to its results, I stumbled across Cube concepts, and even for fun, copied one of my pivots to another sheet, and went into olap tools and converted to formulas to investigate...oh bother, I realized that perhaps I will have to learn that one another time.  If you know of any good tutorials on such, I would greatly appreciate the tip.



I do a good conference presentation on that topic (even if Ido say so myself), but unfortunately I have never recoded it and I don't think any of the conferences did either.


----------



## theBardd (Apr 14, 2019)

Accepting that the data is not shareable and the project is far to complex to solve over these forums, I have had another thoiught that might be the simplest solution for you to be able to take it forward.


I have said many times that I do not understand why you would want to add the pivots back into the datamodel, but assuming that if you did you could work with that and take the project forward, how about this as a solution? If you supply the names of the pivot tables that you want to include, we could write some VBA code that would take those pivots and load them into Excel tables. We could even write code to load them as Power Query queries, but as that is just a once-off, it is probably best that you do it yourself, especially as you would need to work in Power Query to use it anyway. You could add a button to a worksheet to fire the VBA.


----------



## Maggie Barr (Apr 14, 2019)

theBardd,
I am very happy to inform you that your comment of "load them as Power Query queries" made me go back to looking into this option.  I had been able to create reference queries that would show me what I needed, but I was unable to link them through relationships back into the data model to allow for the display of the data from different queries within the same pivot.  Well, I figured it out (mostly)!  I have a locations table lookup that is unique at the Block Name/Code level, but my analyses actually look at the Region and Block Type, not the block name, so I created a reference query that grouped these so that I could then create the relationship needed, but I also had to create a merged columns of the region; block type so that I could base the relationship on that (one to many) but still allow the links to follow through.  That being said, I was still having a hard time getting the two separate queries to put data in by reading the species list accordingly, but then I realized I needed a reference query to the full dataset that created a distinct species list that appears in the data, and then link that to those other two queries based on species, and use that distinct species list as the rows.  Now I can create one pivot table that has the columns from what would have been two pivot tables.  That in and of itself is huge for me! 

I think I am at the point that I may need to know more about writing measures, if that is the tool to use.  I would like to get the percentage of blocks each species is observed in in the pivot as a column.  I created yet another reference query that parsed the data down and provides the # of blocks with data for each region; block type.  I have the # of Blocks observed in for each species in each region; block type in one query, but I need a column (I think in that query) that will look for the # of blocks with data in another query that matches the region; block type field to do the percentage calculation.  I hope this is making some sense, and if it does, I would appreciate any advice.  

Mostly, I just wanted to make sure that I apologized for my delay in responding, and thank you for your continued questioning of why I wanted to do things the way I was, as it was the wrong way.  I just had not actually thought/figured out how to manipulate the raw data and lookup data as reference queries to create the relationships between all the queries to enable the pivot I needed.  Again, thank you for your persistence that I didnt need to do what I thought I did, and for all of your input and effort in helping.
Best Wishes,
Maggie


----------



## Maggie Barr (Apr 11, 2019)

Hello and thank you in advance if you can help,
I am using MS Office 2019 on a PC.  I am somewhat new to Power Query and Power Pivot, and I am trying to analyze a data set in a way that as much of it as possible is "automatic".  I currently have to run multiple pivot tables and bring information from them, by hand, into a final summary table.  I would like to add my pivot tables to the data model so that I could potentially build on the model, and thus be able to bring data from those directly into a final summary.  When I go to insert a pivot table, use this workbook's Data Model, Existing Worksheet, the little box for Add this data to the Data Model is grayed out.  Does anyone know why that is and how I can get the data into the model.  I have all my pivot tables built, and if there were a way to select the table and add it, that would be great, but, if not, I can recreate them, it is just not giving me the option.  My pivot tables do have more than one field as a header (meaning two rows), so I do not know if that will pose a problem.
Any help would be appreciated,
Thank you,
Maggie


----------



## theBardd (Apr 14, 2019)

Maggie Barr said:


> I am very happy to inform you that your comment of "load them as Power Query queries" made me go back to looking into this option. I had been able to create reference queries that would show me what I needed, but I was unable to link them through relationships back into the data model to allow for the display of the data from different queries within the same pivot. Well, I figured it out (mostly)! I have a locations table lookup that is unique at the Block Name/Code level, but my analyses actually look at the Region and Block Type, not the block name, so I created a reference query that grouped these so that I could then create the relationship needed, but I also had to create a merged columns of the region; block type so that I could base the relationship on that (one to many) but still allow the links to follow through. That being said, I was still having a hard time getting the two separate queries to put data in by reading the species list accordingly, but then I realized I needed a reference query to the full dataset that created a distinct species list that appears in the data, and then link that to those other two queries based on species, and use that distinct species list as the rows. Now I can create one pivot table that has the columns from what would have been two pivot tables.





That is a common, and powerful, technique in Power Query. I didn't mention it but in my little tests that I did with your data that is exactly what I was doing. Take a table with say Species in, strip out all columns but the Species, remove duplicates, add an index column, voila we have a Species table that we can add to the datamodel. We can then replace any other tables that have Soecies in them with a SpeciesId by merging with the Species table, extract the id, anbd remove the old text column. Add more relational, usable.  




Maggie Barr said:


> I think I am at the point that I may need to know more about writing measures, if that is the tool to use. I would like to get the percentage of blocks each species is observed in the pivot as a column.




Whilst I would not siscourage you from learning DAX measures, you might not need a measure for that. Take a look at pivots, right-click an item in the pivot list Values pane, and then click the Value Field Settings. There you can see that you can pivot as a percentage of the row or column. More complex stuff does need DAX, and should you have the time, it will add power to your analysis toolbox.




Maggie Barr said:


> I created yet another reference query that parsed the data down and provides the # of blocks with data for each region; block type. I have the # of Blocks observed in for each species in each region; block type in one query, but I need a column (I think in that query) that will look for the # of blocks with data in another query that matches the region; block type field to do the percentage calculation. I hope this is making some sense, and if it does, I would appreciate any advice.




Again, I don't understand why you would do this. If you have a table that contains all of the attributes of a block, the type, region, species, your pivot  can give you the percentage directly. Am I missing something here?


----------



## Maggie Barr (Apr 14, 2019)

theBardd,
So, I simply had to merge one query with the other to get the field into the query needed to calculate the percent.  My problem now is that, when I try to pull that into the Pivot table, it wants to perform a calculation with that % thus changing it from 50% to 0.5.  I do not know if a way to get a pivot to display just the contents of a field you drag in, and from what I have searched over the years and now, there is no way to do it.  Any advice would be appreciated.
Regards,
Maggie


----------



## Maggie Barr (Apr 14, 2019)

Got it, Value field setting, show values as tab, no calculation, number format, % with 2 decimal places.
I do not have the full report format yet, but the huge part of the report is now automatic.
Always so grateful to everyone here at Mr. Excel for their time and effort to help!!!
Best Wishes,
Maggie


----------

