Adding Power Pivot Data to the Data Model?

[Maggie Barr]

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]

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]

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

 

[theBardd]

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]

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]

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]

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

 

[Maggie Barr]

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]

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

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]

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